1

I have created a user on my primary node that has access to all of the databases in Availability Group AG1. How do I ensure that same user has the same rights on all the other replicas in AG1?

According to this MSDN article (http://msdn.microsoft.com/en-us/library/hh270282(v=sql.110).aspx) I need to determine if they are contained databases first but I'm not entirely sure I have contained databases. The user that I created on the other replica's get's an error when I try to add him to the AG1 databases. The error indicates they are part of an Availability Group and are not accessible.

3
  • You can check if your databases are contained pretty easily. SELECT name,containment_desc FROM sys.databases; Nov 4, 2014 at 17:10
  • @AaronBertrand Thanks! It appears none of my databases are contained. Nov 4, 2014 at 18:00
  • Man containded dbs are messy. Create users with same SIDs on sql server level. Nov 5, 2014 at 11:12

2 Answers 2

3

Assuming these are not contained databases, you need to worry about the logins (server level), not the users (database level). Contained users will be transferred automatically without difficulty.

Manually creating a login with the same name on the other server is not good enough - the SID will not match.

Robert Davis has written a post that describes how to generate the login scripts with the right SID and the right password hash. This is what you need to run on each of the replicas:

(Also see the comment thread on this answer.)

In order to just script all of the logins on the local server, change the stored procedure by commenting out this section (and the End that goes with it):

--If Not Exists (Select 1 From sys.server_principals
--          Where name = @LoginName)
--  Begin

(There are other, similar IF NOT EXISTS sections you'll want to comment out in order to get roles and permissions.)

Then call the stored procedure with hard-coded @@SERVERNAME and @debug = 1. This will print out CREATE LOGIN commands with the password hash and you can pick the ones you need and run those commands on the replicas.

Jonathan Kehayias wrote an SSMS add-in that handles all of the uncontained objects (don't forget about things like jobs), but I don't know if it has been maintained for newer versions of SSMS:

4
  • I ran the linked server script to create a linked server to my secondary replica. Now when I run the original script using the name of the linked server as my @PartnerServer parameter, I get the message that the command completed successfully. However, it doesn't appear that the login was created on the replica. Nov 4, 2014 at 18:03
  • @GeoffDawdy Really tough for me to troubleshoot what you ran or where it actually ran. You should be able to massage the output and run it directly on the replicas instead of using the linked servers; this is why I pointed to the comment thread. Nov 4, 2014 at 18:10
  • How does this method differ from method 3 of the linked KB article? It appears the script from the KB article captures the SID from the primary replica and allows me to generate a create login script with the correct SID. Nov 4, 2014 at 20:48
  • @Geoff The method doesn't really differ, except the documentation tells you what to do, but not how to do it. Nov 4, 2014 at 20:50
0

If your not planning on going the contained database route you may want to consider running the procedure sp_helprev_login and in the output find the account you are needing and copying the create user information and run it on the other nodes of the AG.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.