29 thoughts on “Availability Groups: How to sync logins between replicas

  1. Hi , I was testing this, but it fails (error below) specifically on the code below as the password_hash on sql for user type S is NULL (what do you recommend?)

    Code:
    INSERT INTO @queue ([sql])
    SELECT N’
    CREATE LOGIN [‘+p.[name]+’] ‘+(CASE
    WHEN p.[type]=’S’
    THEN N’WITH PASSWORD=0x’+CONVERT(nvarchar(max), p.password_hash, 2)+N’ HASHED, CHECK_POLICY=OFF, SID=0x’+CONVERT(nvarchar(max), p.[sid], 2)+N’, ‘
    WHEN p.[type] IN (‘U’, ‘G’)
    THEN N’FROM WINDOWS WITH ‘ END)+
    N’DEFAULT_DATABASE=[‘+p.default_database_name+N’]’+
    ISNULL(N’, DEFAULT_LANGUAGE=’+p.default_language_name, N”)+N’;’
    FROM @primaryLogins AS p
    WHERE p.[sid] NOT IN (SELECT [sid] FROM master.sys.server_principals) AND
    p.[type] IN (‘U’, ‘G’, ‘S’);

    Error:
    Msg 515, Level 16, State 2, Procedure LMC_SyncLogins, Line 166
    Cannot insert the value NULL into column ‘sql’, table ‘@queue’; column does not allow nulls. INSERT fails.

      • Hi Daniel,
        Thanks for your prompt response , all the sql_logins have password_has NULL, this is SQL Server 2014 Enterprise, I can’t exclude them because I need to recreate them on the secondary AG, any advise ?

        Thanks again

        • My question was if you’re using contained database users (i.e. users that are local to the database and don’t have a login)? Or, maybe, it’s a permission issue that the security context that you’re using doesn’t have sufficient permissions?

          • Hi Daniel,
            You are right, it was the security context, once I gave sysadmin to the Linked server user then I see the password Hash. Now in order to restrict the permissions to this user, before assigning the sysadmin role, this user has securityadmin, serveradmin, db_owner over master system database, but it was not enough to see the password_hash, any advise on restricting security rights to be able to see the hash? besides the initial rights

            Thanks again

  2. The procedure does not transfer any database-level permissions, it only creates server-level logins – totally useless

    • It’s not supposed to. I’m not sure what you were expecting, but in an AG, the database-level permissions are synced by the AG. The procedure transfers *server-level* principals.

  3. Hi,

    How far are you with the other articles?
    “logins, SQL Server Agent jobs, SSIS packages stored in SQL Server, linked servers and server settings”
    I could use all them currently :)

  4. Thanks for the tip. Just a note that there’s a typo in your code – you need to add an “=” after the WITH PASSWORD clause in your section titled “Here’s how to get the information you need from the primary replica:”.

  5. I get the same error that the first response mentions. However, this is a brand new AG and all I have added was one SQL login and a windows login besides the Admin accounts.
    I receive this error:
    Msg 515, Level 16, State 2, Procedure SyncLogins, Line 175
    Cannot insert the value NULL into column ‘sql’, table ‘@queue’; column does not allow nulls. INSERT fails.
    The statement has been terminated.

  6. Hi, we’ve written a free (for 2 instances) tool for comparing servers which you may find useful after you’ve used these scripts. We compare a long list of configuration settings and even fix the orphaned users in the Advise module (free for 30 days).

  7. Hi Daniel – I’m getting the following errors when running the stored procedure. I’ve attempted to turn the CHECK_POLICY off on the three users that are throwing the error but am still receiving the error. Any ideas?

    Msg 15025, Level 16, State 1, Line 5
    The server principal ‘##MS_PolicyEventProcessingLogin##’ already exists.
    Msg 33020, Level 16, State 1, Line 7
    A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
    Msg 33020, Level 16, State 1, Line 8
    A HASHED password cannot be set for a login that has CHECK_POLICY turned on.
    Msg 33020, Level 16, State 1, Line 9
    A HASHED password cannot be set for a login that has CHECK_POLICY turned on.

    • Huh, that’s odd. Haven’t seen that before. I would try googling the error message and see if that leads you anywhere.
      Did you turn off CHECK_POLICY on both/all servers?

  8. Hi Daniel,
    here in script you are fatching the name of primary replica first and then using it all over in script so it automatic fetch detail from primary replica so in here what is the use Linked server if i am executing this on local or secondry replica?

    • The reason I’m using a linked server to access the primary replica is because the primary replica is the “master” record for the logins. If the Availability Group fails over to another replica, that server will now be the primary – thus the need to set up the linked servers on all replicas.

      Was that what you meant by your question?

    • Thanks Daniel ,

      I got the use of linked server but one small issue I am facing at moment once i run the job which execute this procedure will move logins correct and those login have correct db level access to secondary replica but its not moving fix server role access . Please let me know in case I am missing anything in that ..I just modified your code as below -:

      USE [master]
      GO
      create PROCEDURE [dbo].[SyncLogins_test]
      @primary_replica sysname=NULL,
      — @allow_drop_logins bit=0,
      @print_only bit=0
      AS
      SET NOCOUNT ON;
      DECLARE @sql nvarchar(max), @msg nvarchar(max);
      IF (@primary_replica IS NULL) BEGIN;
      SELECT @primary_replica=primary_replica
      FROM sys.dm_hadr_availability_group_states;

      IF (@@ROWCOUNT>1) BEGIN;
      THROW 50000, N’More than one availability group exists on server, please specify @primary_replica.’, 16;
      RETURN;
      END;

      IF (@primary_replica IS NULL) BEGIN;
      THROW 50000, N’No availability group found, please specify @primary_replica.’, 16;
      RETURN;
      END;
      END;

      IF (@primary_replica=@@SERVERNAME) BEGIN;
      PRINT N’This server is the primary replica. No changes will be made.’;
      RETURN;
      END;

      — These are the logins (Windows user and groups, SQL logins) from
      — the primary replica.
      DECLARE @primaryLogins TABLE (
      [name] sysname NOT NULL,
      [sid] varbinary(85) NOT NULL,
      [type] char(1) NOT NULL,
      is_disabled bit NULL,
      default_database_name sysname NULL,
      default_language_name sysname NULL,
      is_policy_checked bit NULL,
      is_expiration_checked bit NULL,
      password_hash varbinary(256) NULL,
      PRIMARY KEY CLUSTERED ([sid])
      );

      SET @sql=N’
      SELECT sp.[name], sp.[sid], sp.[type], sp.is_disabled, sp.default_database_name,
      sp.default_language_name, l.is_policy_checked, l.is_expiration_checked, l.password_hash
      FROM [‘+@primary_replica+’].master.sys.server_principals AS sp
      LEFT JOIN [‘+@primary_replica+’].master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
      WHERE sp.[type] IN (”U”, ”G”, ”S”) AND
      sp.[name] NOT LIKE ”NT Service\%” AND
      sp.[name] NOT IN (”NT AUTHORITY\SYSTEM”) AND SP.NAME NOT IN(SELECT SP2.NAME FROM master.sys.server_principals AS sp2 )’;

      INSERT INTO @primaryLogins
      EXECUTE master.sys.sp_executesql @sql;

      — These are the server roles on the primary replica.
      DECLARE @primaryRoles TABLE (
      [sid] varbinary(85) NOT NULL,
      [name] sysname NOT NULL,
      PRIMARY KEY CLUSTERED ([sid])
      );

      SET @sql=N’
      SELECT sr.[sid], sr.[name]
      FROM [‘+@primary_replica+’].master.sys.server_principals AS sr
      WHERE sr.is_fixed_role=0 AND
      sr.[type]=”R”’ ;

      INSERT INTO @primaryRoles
      EXECUTE master.sys.sp_executesql @sql;

      — These are the role members of the server roles on
      — the primary replica.
      DECLARE @primaryMembers TABLE (
      role_sid varbinary(85) NOT NULL,
      member_sid varbinary(85) NOT NULL,
      PRIMARY KEY CLUSTERED (role_sid, member_sid)
      );

      SET @sql=N’
      SELECT r.[sid], m.[sid]
      FROM [‘+@primary_replica+N’].master.sys.server_principals AS r
      INNER JOIN [‘+@primary_replica+N’].master.sys.server_role_members AS rm ON r.principal_id=rm.role_principal_id
      INNER JOIN [‘+@primary_replica+N’].master.sys.server_principals AS m ON rm.member_principal_id=m.principal_id’;

      INSERT INTO @primaryMembers
      EXECUTE master.sys.sp_executesql @sql;

      — These are the server-level permissions on the
      — primary replica.
      DECLARE @primaryPermissions TABLE (
      state_desc nvarchar(120) NOT NULL,
      [permission_name] nvarchar(256) NOT NULL,
      principal_name sysname NOT NULL,
      PRIMARY KEY CLUSTERED ([permission_name], principal_name)
      );

      SET @sql=N’
      SELECT p.state_desc, p.[permission_name], sp.[name]
      FROM [‘+@primary_replica+’].master.sys.server_permissions AS p
      INNER JOIN [‘+@primary_replica+’].master.sys.server_principals AS sp ON p.grantee_principal_id=sp.principal_id
      WHERE p.class=100 AND SP.NAME NOT IN(SELECT SP2.NAME FROM master.sys.server_principals AS sp2 )’;

      INSERT INTO @primaryPermissions
      EXECUTE master.sys.sp_executesql @sql;

      — This table variable contains the “run queue” of all commands
      — we want to execute on the local (secondary) replica, ordered
      — by “seq”.
      DECLARE @queue TABLE (
      seq int IDENTITY(1, 1) NOT NULL,
      [sql] nvarchar(max) NOT NULL,
      PRIMARY KEY CLUSTERED (seq)
      );

      — Login doesn’t exist on the secondary – CREATE.
      INSERT INTO @queue ([sql])
      SELECT N’
      CREATE LOGIN [‘+p.[name]+’] ‘+(CASE
      WHEN p.[type]=’S’
      THEN N’WITH PASSWORD=0x’+CONVERT(nvarchar(max), p.password_hash, 2)+N’ HASHED, CHECK_POLICY=OFF, SID=0x’+CONVERT(nvarchar(max), p.[sid], 2)+N’, ‘
      WHEN p.[type] IN (‘U’, ‘G’)
      THEN N’FROM WINDOWS WITH ‘ END)+
      N’DEFAULT_DATABASE=[‘+p.default_database_name+N’]’+
      ISNULL(N’, DEFAULT_LANGUAGE=’+p.default_language_name, N”)+N’;’
      FROM @primaryLogins AS p
      WHERE p.[sid] NOT IN (SELECT [sid] FROM master.sys.server_principals) AND
      p.[type] IN (‘U’, ‘G’, ‘S’); –and p.password_hash is not null;

      — Login exists but has been enabled/disabled – ALTER.
      INSERT INTO @queue ([sql])
      SELECT N’
      ALTER LOGIN [‘+ISNULL(sp.[name], x.[name])+’]’+
      (CASE WHEN x.is_disabled=0 AND sp.is_disabled=1 THEN N’ ENABLE’
      WHEN x.is_disabled=1 AND (sp.is_disabled=0 OR sp.[sid] IS NULL) THEN N’ DISABLE’ END)+N’;’
      FROM @primaryLogins AS x
      LEFT JOIN master.sys.server_principals AS sp ON x.[sid]=sp.[sid]
      WHERE x.is_disabled!=sp.is_disabled OR
      x.is_disabled=1 AND sp.[sid] IS NULL;

      — Login exists but has changed in some respect – ALTER.
      INSERT INTO @queue ([sql])
      SELECT N’
      ALTER LOGIN [‘+sp.[name]+’] WITH ‘+
      SUBSTRING(
      (CASE WHEN x.password_hash!=l.password_hash
      THEN N’, PASSWORD=0x’+CONVERT(nvarchar(max), x.password_hash, 2)+N’ HASHED’
      ELSE N” END)+
      (CASE WHEN ISNULL(x.default_database_name, N’master’)!=ISNULL(sp.default_database_name, N’master’)
      THEN ‘, DEFAULT_DATABASE=[‘+x.default_database_name+N’]’
      ELSE N” END)+
      (CASE WHEN x.default_language_name!=sp.default_language_name
      THEN ‘, DEFAULT_LANGUAGE=’+x.default_language_name
      ELSE N” END)+
      (CASE WHEN x.[name]!=sp.[name]
      THEN ‘, NAME=[‘+x.[name]+N’]’
      ELSE N” END)+
      (CASE WHEN x.is_policy_checked!=l.is_policy_checked
      THEN ‘, CHECK_POLICY=’+(CASE x.is_policy_checked WHEN 1 THEN N’ON’ ELSE N’OFF’ END)
      ELSE N” END)+
      (CASE WHEN x.is_expiration_checked!=l.is_expiration_checked
      THEN ‘, CHECK_EXPIRATION=’+(CASE x.is_expiration_checked WHEN 1 THEN N’ON’ ELSE N’OFF’ END)
      ELSE N” END), 3, 10000)+N’;’
      FROM @primaryLogins AS x
      INNER JOIN master.sys.server_principals AS sp ON x.[sid]=sp.[sid]
      LEFT JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid]
      WHERE x.password_hash!=l.password_hash OR
      ISNULL(x.default_database_name, N’master’)!=ISNULL(sp.default_database_name, N’master’) OR
      ISNULL(x.default_language_name, N’us_english’)!=ISNULL(sp.default_language_name, N’us_english’) OR
      x.[name]!=sp.[name] OR
      ISNULL(x.is_policy_checked, 0)!=ISNULL(l.is_policy_checked, 0) OR
      ISNULL(x.is_expiration_checked, 0)!=ISNULL(l.is_expiration_checked, 0);

      — Roles that don’t exist on the secondary – CREATE.
      INSERT INTO @queue ([sql])
      SELECT N’
      CREATE SERVER ROLE [‘+r.[name]+N’];’
      FROM @primaryRoles AS r
      WHERE [sid] NOT IN (
      SELECT [sid]
      FROM sys.server_principals
      WHERE is_fixed_role=0 AND
      [type]=’R’);

      — Add server role memberships:
      INSERT INTO @queue ([sql])
      SELECT N’
      ALTER SERVER ROLE [‘+pr.[name]+N’] ADD MEMBER [‘+pl.[name]+N’];’
      FROM @primaryMembers AS pm
      INNER JOIN @primaryLogins AS pl ON pm.member_sid=pl.[sid]
      INNER JOIN @primaryRoles AS pr ON pm.role_sid=pr.[sid]
      LEFT JOIN sys.server_principals AS r ON pm.role_sid=r.[sid] AND r.[type]=’R’
      LEFT JOIN sys.server_principals AS m ON pm.member_sid=m.[sid]
      LEFT JOIN sys.server_role_members AS rm ON r.principal_id=rm.role_principal_id AND m.principal_id=rm.member_principal_id
      WHERE rm.role_principal_id IS NULL;

      ——————————————————————————-
      — GRANT/DENY server-level permissions:
      INSERT INTO @queue ([sql])
      SELECT N’
      ‘+pp.state_desc+N’ ‘+pp.[permission_name]+N’ TO [‘+pp.principal_name+’];’
      FROM @primaryPermissions AS pp
      INNER JOIN sys.server_principals AS sp ON pp.principal_name=sp.[name]
      LEFT JOIN sys.server_permissions AS p ON
      p.grantee_principal_id=sp.principal_id AND
      p.[permission_name] COLLATE database_default=pp.[permission_name] AND
      p.class=100
      WHERE pp.state_desc!=p.state_desc COLLATE database_default;

      ——————————————————————————-
      — Ready to roll:

      SET @sql=N”;
      SELECT @sql=@sql+[sql] FROM @queue ORDER BY seq;

      — @print_only=1: PRINT the queue.
      WHILE (@print_only=1 AND @sql!=N”) BEGIN;
      PRINT LEFT(@sql, CHARINDEX(CHAR(13), @sql+CHAR(13))-1);
      SET @sql=SUBSTRING(@sql, CHARINDEX(CHAR(13), @sql+CHAR(13))+2, LEN(@sql));
      END;

      — @print_only=0: Execute the queue.
      IF (@print_only=0)
      EXECUTE master.sys.sp_executesql @sql;

      GO

  9. Hi Daniel,
    When we create a server role ‘A’ on Primary replica and create the same on the secondary replica , they both will have different SID. so when I run the synclogin proc on the secondary replica ,It gives me CREATE role A even though the role exists on the secondary replica because its comparing SIDs.

  10. Also when we change a server role of login the primary replica , the procedure on the secondary replica doesn’t identify that change. Where does SQL store the login and the sever role link information?

  11. Hi Daniel,
    I did a few changes to the code as follows. Hope it helps. I am basically not using SID and using the name instead. Also in this code I am comparing the secondary node server role access with primary nodes sever role access.
    ——————————————————————————-
    — Roles that don’t exist on the primary – DROP.
    INSERT INTO @queue ([sql])
    SELECT N’
    DROP ROLE [‘+sp.[name]+N’];’
    FROM master.sys.server_principals AS sp
    WHERE is_fixed_role=0 AND
    sp.[type]=’R’ AND
    sp.[name] NOT IN (SELECT [name] FROM @primaryRoles);

    — Roles that don’t exist on the secondary – CREATE.
    INSERT INTO @queue ([sql])
    SELECT N’
    CREATE SERVER ROLE [‘+r.[name]+N’];’
    FROM @primaryRoles AS r
    WHERE [name] NOT IN (
    SELECT [name]
    FROM sys.server_principals
    WHERE is_fixed_role=0 AND
    [type]=’R’);
    ——————————————————————————-
    — Add missing role membership
    DECLARE @missingrolemembership TABLE (
    [accname] sysname NOT NULL,
    [rolename] sysname NOT NULL
    );

    SET @sql=N’SELECT sp1.name,spr1.name from [‘+@primary_replica+’].master.sys.server_principals sp1
    JOIN [‘+@primary_replica+’].master.sys.server_role_members srm1 ON (sp1.principal_id = srm1.member_principal_id)
    JOIN [‘+@primary_replica+’].master.sys.server_principals spr1 on (spr1.principal_id = srm1.role_principal_id)
    WHERE NOT EXISTS
    (
    SELECT sp.name,spr.name from master.sys.server_principals sp
    JOIN master.sys.server_role_members srm ON (sp.principal_id = srm.member_principal_id)
    JOIN master.sys.server_principals spr on (spr.principal_id = srm.role_principal_id)
    )’

    INSERT INTO @missingrolemembership
    EXECUTE master.sys.sp_executesql @sql;

    INSERT INTO @queue ([sql])
    SELECT N’
    ALTER SERVER ROLE [‘+[rolename]+N’] ADD MEMBER [‘+[accname]+N’];’
    FROM @missingrolemembership

    ——————————————————————————-
    — revoke role membership that exist on secondary and not in primary
    DECLARE @revokerolemembership TABLE (
    [accname] sysname NOT NULL,
    [rolename] sysname NOT NULL
    );

    SET @sql=N’SELECT sp.name,spr.name from master.sys.server_principals sp
    JOIN master.sys.server_role_members srm ON (sp.principal_id = srm.member_principal_id)
    JOIN master.sys.server_principals spr on (spr.principal_id = srm.role_principal_id)
    WHERE NOT EXISTS
    (
    SELECT sp1.name,spr1.name from [‘+@primary_replica+’].master.sys.server_principals sp1
    JOIN [‘+@primary_replica+’].master.sys.server_role_members srm1 ON (sp1.principal_id = srm1.member_principal_id)
    JOIN [‘+@primary_replica+’].master.sys.server_principals spr1 on (spr1.principal_id = srm1.role_principal_id)
    )’

    INSERT INTO @revokerolemembership
    EXECUTE master.sys.sp_executesql @sql;

    INSERT INTO @queue ([sql])
    SELECT N’
    ALTER SERVER ROLE [‘+[rolename]+N’] DROP MEMBER [‘+[accname]+N’];’
    FROM @revokerolemembership

    — missing Server level role permission
    DECLARE @missingserverrolepermission TABLE (
    state_desc nvarchar(120) NOT NULL,
    [permission_name] nvarchar(256) NOT NULL,
    principal_name sysname NOT NULL,
    PRIMARY KEY CLUSTERED ([permission_name], principal_name)
    );

    SET @sql=N’SELECT p.state_desc, p.[permission_name], sp.[name]
    FROM [‘+@primary_replica+’].master.sys.server_permissions AS p
    INNER JOIN [‘+@primary_replica+’].master.sys.server_principals AS sp ON p.grantee_principal_id=sp.principal_id
    WHERE p.class=100 and sp.type =”R”
    AND NOT EXISTS (
    SELECT p1.state_desc, p1.[permission_name], sp1.[name]
    FROM master.sys.server_permissions AS p1
    INNER JOIN .master.sys.server_principals AS sp1 ON p1.grantee_principal_id=sp1.principal_id
    WHERE p1.class=100 and sp1.type =”R”
    AND sp1.[name] = sp.[name]
    )’
    —- Revoke server role permission that exist on secondary and not in primary
    DECLARE @revokeserverrolepermission TABLE (
    state_desc nvarchar(120) NOT NULL,
    [permission_name] nvarchar(256) NOT NULL,
    principal_name sysname NOT NULL,
    PRIMARY KEY CLUSTERED ([permission_name], principal_name)
    );

    SET @sql=N’SELECT CASE WHEN p.state_desc =”GRANT” THEN ”DENY” ELSE ”GRANT” END, p.[permission_name], sp.[name]
    FROM master.sys.server_permissions AS p
    INNER JOIN .master.sys.server_principals AS sp ON p.grantee_principal_id=sp.principal_id
    WHERE p.class=100 and sp.type =”R”
    AND NOT EXISTS (
    SELECT p1.state_desc, p1.[permission_name], sp1.[name]
    FROM [‘+@primary_replica+’].master.sys.server_permissions AS p1
    INNER JOIN [‘+@primary_replica+’].master.sys.server_principals AS sp1 ON p1.grantee_principal_id=sp1.principal_id
    WHERE p1.class=100 and sp1.type =”R”
    AND sp1.[name] = sp.[name]
    AND p1.[permission_name] = p.permission_name
    )’

    INSERT INTO @revokeserverrolepermission
    EXECUTE master.sys.sp_executesql @sql;

Let me hear your thoughts!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s