Grant User Read Access to Secondary Replica

  • alex.sqldba

    SSChampion

    Points: 10045

    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

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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: 10045

    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: 1004424

    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: 10045

    Marvellous! Thanks a lot Gail.

  • ranuganti

    Ten Centuries

    Points: 1092

    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

    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,

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

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