Grant User Read Access to Secondary Replica

  • 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

  • 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
  • Cheers Gail.

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

    Alex

  • 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
  • Marvellous! Thanks a lot Gail.

  • 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!

  • 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,

  • 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

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

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