Grant User Read Access to Secondary Replica

Question

Grant User Read Access to Secondary Replica

alex.sqldba

SSChampion

Points: 10254
More actions
September 15, 2015 at 3:58 am

#321243

Hi Guys,
I'm a bit hazy on this aspect of AlwaysOn.
I have 3 servers taking part in an AlwaysOn AG.
I want to grant a user READ access to only one replica, and certainly not to the principle.
How do I go about doing this?
Cheers
Alex

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Create the login and user on the Primary replica. Grant the user the rights you want them to have on the secondary replica. Deny the login CONNECT rights on the primary replica.

Create the login (with the same SID and password) on the secondary replica you want them to be able to read. The availability group will transfer the user and the database permissions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alex.sqldba SSChampion Points: 10254 More actions · Answer 2

Cheers Gail.

So DENY CONNECT permission wont be sent accross because they are server level and not DB level?

Alex

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Correct. The availability group is at the database level. Server-level permissions are kept in master and that's not going to be part of your AG.

Just don't change any of the database user's permissions on the primary as those will be transferred.

p.s. The 'With SID' portion is critically important. If you don't, the user will be orphaned on the secondary and the person won't be able to get access. So you need to CREATE LOGIN ... SID = ... on the secondary, explicitly specifying the SID that the login has on the primary. This is for SQL logins. For Windows logins, the SID comes from AD and hence will be the same on both servers automatically.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alex.sqldba SSChampion Points: 10254 More actions · Answer 4

alex.sqldba

SSChampion

Points: 10254

September 15, 2015 at 4:22 am

#1826680

Marvellous! Thanks a lot Gail.

ranuganti Ten Centuries Points: 1092 More actions · Answer 5

Hi Gail,

What if we have 2 SQL servers, 2 listeners, and both the SQL servers are having read only databases.

EX: the user should login to SQL01 and read DB2, DB3 tables. and also should login to SQL02 and read DB1 tables. How can we achieve this?

SQL01

DB1 (primary)

DB2 (secondary)

DB3 (secondary)

DB4

DB5

SQL02

DB1 (secondary)

DB2 (Primary)

DB3 (primary)

DB7

Thanks a Lot!

Avijeet Old Hand Points: 381 More actions · Answer 6

Hi Gail,

In SQL Server 2016, if we DENY Connect to login on Primary Replica, the user cannot connect to secondary replicas as well using Listeners.

Did you checked connecting to secondary using listeners ?

As Listeners, are necessary to ensure there is no impact to anyone when role switch happens.

Please suggest....

Regards,

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

This is also didn't work for me - it said the database was inaccessible. When I GRANTed CONNECT on the primary, it worked, though. I wasn't happy with that, so I dropped the login (but not the database user) on the primary. It worked after that.

(In case anyone's wondering, I am indeed aware that this is an old thread.)

John