July 2, 2014 at 3:31 pm
I have an availability group with read only replicas, readable secondary set to yes and allow all incoming connections. I have also configured the read only routing (at least I'm pretty sure this is correct).
If I login to SSMS with a user in the sysadmin role I can view the objects in the read only replica database. If I login with a user in the public role I'm unable to get past the obvious error:
"The database databaseA is not accessbile. (ObjectExplorer)"
I've also tried adding the "ApplicationIntent=ReadOnly" option.
Does anyone have an idea on what might be going wrong?
Thanks
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
July 2, 2014 at 3:36 pm
One thing I'm noticing is that when I set this up, the user(s) in question were created after the AG was configured. So user1 on the primary was able to have the datareader permission set, but user1 when created on the replica isn't (for obvious reasons).
So I'm thinking if I perform a quick failover, modify the user1 permissions on the replica, then fail back, this might fix the problem? :unsure:
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
July 2, 2014 at 3:52 pm
sql4gnt (7/2/2014)
So I'm thinking if I perform a quick failover, modify the user1 permissions on the replica, then fail back, this might fix the problem? :unsure:
Negative, because the user or role already exists.
So if anyone has some info or can point me in the right direction, I'd appreciate it.
Thanks
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
July 2, 2014 at 4:42 pm
Posted out to #sqlhelp and thanks to @SQLHA, @Kendra_Little and @SQLSoldier I was able to fix the issue.
The SIDs were different across the servers, so as a quick fix I ran sp_help_revlogin which can be found here http://support.microsoft.com/kb/918992
I'll be testing out the script from Mr. Davis and adding it as a step for configuring replicas. The script is found here http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
Cheers
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
July 2, 2014 at 4:45 pm
and no...im not talking to myself :crazy:
Why is it that people who can't take advice always insist on giving it? - James Bond, Casino Royale
April 23, 2024 at 11:20 am
This was removed by the editor as SPAM
May 11, 2024 at 10:13 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply