November 13, 2013 at 1:56 am
I have 2 SQL Server Instances 01 & 02. The 01 instance is having a DB called xxxUI and 02 is having DB called xxxRPT. We have defined Availability Groups in both the Instances and xxxRPT is added as Secondary DB for 01 instance and xxxUI is added as Secondary DB for 01 Instance.
I have created a Login User1 in Instance01 and I have give db_reader and db_writer permission to xxxUI DB. I have created the same login in Instance02 and the same permission has been give to xxxRPT DB. There are synonyms in xxxRPTDB that points to xxxUI DB and vice versa.
I am able to query the tables from xxxRPT DB from instance 01. However, I am getting the following error message when i try to query a table in xxxUI DB from xxxRPT DB in instance 02.
The server principal "User1" is not able to access the database "xxxUI" under the current security context.
I have given same level of permission. However one is working and another one is throwing this error message. Please let me know what could be the issue
November 13, 2013 at 8:40 am
When you say " I have created the same login in Instance02", are you sure you've created the login with the same SID, not just the name?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply