My general stuff. This will generally include computer related information, but may also contain general rantings on my part.

Tuesday, March 22, 2016

One method to synchronize SQL users in AlwaysOn groups

I have recently started working with AlwaysOn availability groups. The suggested method to deal with users in AlwaysOn groups is with partially contained databases, then the user is in the DB only, which is replicated to all nodes. However, there are some situations where you cant make a DB a contained DB.

One of the things I like to do with SQL is create a read-only SQL user for reporting name 'ReportUser'. Not knowing the intricacies of AlwaysOn, I created the SQL user on both nodes using traditional SQL. This worked at first, but when I failed over the node, my access stopped working.

This returned the SID of my ReportUser account in the database. I failed the DB over to the other server and then deleted the ReportUser account from my system.
Then, I recreated my user, but this time specifying the SID:CREATE LOGIN [ReportUser] WITH PASSWORD = 'oiu234rno89aSdf', SID = 0x144186B7023F4D43BDA437284FB3E1D9, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Now, since the SIDs match on the two servers, when the DB migrates between the servers, my account works.