AlwaysOn Availability Groups are a reasonably simple way to set up disaster recovery (DR) for your SQL Server environment, and with fairly little effort, you can get a bit of high availability (HA) from it as well. But there are a few gotchas, the most obvious of them being that Availability Groups only synchronize specific user-databases, not the entire server setup.
Things that are not included in AGs include logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings. You could sync these manually (as is often the case), but wouldn’t you just love to have an automated process do all this for you?
In this post, we’ll look at logins. For the sake of simplicity, I’ll assume that you have a primary replica with a single AG and any number of secondary replicas. The logic holds true if you have multiple AGs, it just gets trickier.
EDIT: Since I wrote this post, quite a few years ago, I learned about dbaTools, which is an amazing collection of Powershell commandlets for DBAs. One of those allows you to synchronize not only logins, but tons of other things that are not covered in this post.
If you can, I would advise you to use dbaTools to synchronize your stuff instead of this script. However, some companies can’t or won’t use dbaTools, so I’ll leave this post up for posterity.
Quick recap on logins and users
There are two types of so-called security principals you need to keep track of. The first one is the login, which is the server-level principal, the one that your clients and apps log on with. Each login can map to any number of users, one for each database. Users, thus, are referred to as database principals. The login connects to the user with a SID, a security identifier, which is a unique binary string.
For Windows authenticated logins, the SID is created from the Windows user or group, whereas SQL Server authenticated accounts cannot inherit a SID, so a new one is created in SQL Server.
Server-level principals (logins) can be members of server roles and have server-level permissions. Database principals (users), on the other hand, can be members of database roles and have permissions to database objects.
Why sync logins?
If you fail over your Availability Group, whether planned or unplanned, you’ll probably want to be as close to “business as usual” as possible after the failover, meaning that users can still log on to their databases or applications as usual.
Whether things are on fire or you’re just applying a service pack, this should be one less issue for you to deal with.
What do I need to sync?
So we have:
- Server-level principals (logins)
- Server roles
- Server role memberships
- Server-level permissions
- Database-level principals (users)
- Database roles
- Database role memberships
- Database object permissions
- Contained users (special users without logins)
Users, database roles as well as their respective memberships and permissions in the databases are all contained in each respective database. This means that if you make any change in the database, it’s already replicated across the Availability Group.
This means that you’ll only need to take care of the server-level stuff, the first four things on the list.
Windows logins and groups
As I mentioned above, Windows/AD logins and groups already come with a SID (which is what connects logins to users). This means that you can create a login for a given Windows account on any number of servers, and all those logins will get the same SID.
Another advantage with Windows logins are that you don’t have to know the password because Windows or Active Directory manages that for you.
This query will show you how to generate CREATE LOGIN statements for all Windows users and groups:
SELECT N'CREATE LOGIN ['+sp.[name]+'] FROM WINDOWS;' FROM master.sys.server_principals AS sp WHERE sp.[type] IN ('U', 'G') AND sp.is_disabled=0 AND sp.[name] NOT LIKE 'NT [AS]%\%';
SQL Server logins
SQL Server logins generate a new SID when they are created, so if you just create a login on two servers, they’ll end up with different SIDs. To manage this, you can actually manually specify the SID of a SQL Server login when you create it:
CREATE LOGIN [login_name] WITH PASSWORD='Great password', SID=0x02963F...18A3DECEBE;
You can read the SID from the primary replica’s sys.server_principals DMV and then explicitly state that SID when you’re creating the same login on all your secondary replicas.
While you’re at it, read the hashed password from sys.sql_logins. That way, you don’t have to know or write down the passwords – you can just synchronize the hashed passwords between replicas.
CREATE LOGIN [login_name] WITH PASSWORD=0x0200A6860363412...478650 HASHED, CHECK_POLICY=OFF, SID=0x02963F...18A3DECEBE;
Here’s how to get the information you need from the primary replica:
SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+ CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, CHECK_POLICY=OFF, '+ N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' FROM master.sys.server_principals AS sp INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid] WHERE sp.[type]='S' AND sp.is_disabled=0;
If you use user-defined server roles, you may want to set them up on the secondary replicas as well. Here’s a list of your user-defined server roles from the primary replica:
SELECT N'CREATE SERVER ROLE ['+sp.[name]+N'];' FROM sys.server_principals AS sp WHERE sp.principal_id>=100 AND sp.[type]='R';
And here are the server-level permissions for all types of server-level principals:
--- GRANT/DENY (permission) TO principal SELECT p.state_desc+N' '+p.[permission_name]+N' TO ['+sp.[name] COLLATE database_default+N'];' FROM sys.server_permissions AS p INNER JOIN sys.server_principals AS sp ON p.grantee_principal_id=sp.principal_id;
Putting it all together
I thought you’d never ask. I’ve created a stored procedure that will fetch all logins, server roles and server role memberships from a primary replica and sync those to the local (secondary) replica.
Head on over to the Downloads page and get SyncLogins.
You’ll need a linked server from your secondary replica to the primary replica. This linked server should feature the absolute bare-minimum of permissions, preferably with only the “be made using the login’s current security context” selected.
The account running the procedure (or the mapped login in the linked server) will need SELECT access to the following DMVs on the remote (primary) server:
The local account obviously also needs permissions to add/drop logins and assign server role memberships.
Running the sync procedure
The stored procedure runs on the secondary replica and accepts three (optional) parameters:
- @primary_replica: a linked server that points to a SQL Server instance from which you want to read logins and server roles. If left blank, the procedure will try to select the primary replica from an Availability Group. This will fail if there is no Availability Group or if there is more than one.
- @allow_drop_logins: set to 1 if you trust the procedure to drop existing logins on the local instance. Note: if an login is dropped and recreated on the primary, you’ll run into problems if you can’t drop the local login.
- @print_only: set to 1 to only output all changes as T-SQL code instead of executing the changes.
Some features are yet-to-do:
- Support for credential-based logins
- Setting the correct owning principal when creating a login
- Altering permissions to endpoints
EDIT: As of November 2018, this script is on GitHub, so you can fork your own copy, make improvements to it, and send me a pull request!