76 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.

    Reply

      • 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

        Reply

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

          Reply

          • 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

            Reply

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

    Reply

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

      Reply

  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 :)

    Reply

  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:”.

    Reply

  6. 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.

    Reply

  7. 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).

    Reply

  8. 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.

    Reply

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

      Reply

  10. 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?

    Reply

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

      Reply

    • 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

      Reply

  11. 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.

    Reply

  12. 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?

    Reply

  13. 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;

    Reply

  14. Hi Daniel.
    This is a great post identifying all elements required to sync from primary to secondary.
    This has been on my to-do list for a few months now.
    My gotya is that we load balance across the two nodes so a password that has been changed (new password or user) is on which node? OK obviously the primary node of that Availability Group. But when you have 8 Availability Groups that are load balanced between nodes…
    I now have to get down to the databases at AG Primary Node level.
    I’ll post what I come up with to get feedback and at least share with you.
    Thank you Omar. the inclusion of permission level grants is important as they are often missed.

    Reply

  15. Hi Daniel – I just ran across your script. Very nicely done, however I needed to make a couple of change to support the granting/revoke of all server roles.

    1. I removed the reference to is_fixed_role so that it would replicate all roles granted to a user.
    2. In order for proc to be able to revoke the server roles, the drop login code needed to be at the end of the script so that it is the last thing executed – otherwise you get an error when it tries to revoke server role permissions because the login was already gone.
    3. I removed the ability to copy/drop sa and any ##%## logins

    Again, nicely done sir.

    Reply

      • Hi Daniel,

        Thanks for this code!
        Like Patrick, I also think it is important to make sure that the sa and ## users and perhaps NO sysadmins are removed, since running with wrong linked server permissions could be catastrophic and it is one thing to lose your other logins – quite another to lose that and administrative users.

        Also, would recommend not logins not be removed from the sysadmin role – I have had cases where a user is in sysadmin on both nodes but the code thinks the primary login is not and would try to remove it from sysadmin on secondary. Perhaps add another parameter @AllowRoleRemoval or have that code block follow the setting of @allow_drop_logins so that if not dropping logins we are also not removing them from roles. If this code gets run from a non sysadmin it wont see all rights and will behave destructively. I would rather know that if I want to remove a user from sysadmin I have to manually do that on both nodes.

        I also put code in that checks that it sees more than a few logins before continuing just in case the linked server user permissions have changed and would cause everything to be dropped.

        And I had an odd circumstance at the section of code that would print ‘This server is the primary replica. No changes will be made.’ where the @@servername returned NULL which causes the script to continue on the primary (luckily it was being run as print_only). I changed the code that looks for the primary from IF (@primary_replica=@@SERVERNAME) to: IF (select
        ars.role_desc
        from sys.dm_hadr_availability_replica_states ars
        inner join sys.availability_groups ag
        on ars.group_id = ag.group_id
        where ag.name = (SELECT Name FROM sys.availability_groups)
        and ars.is_local = 1) = ‘PRIMARY’
        which returned the correct value for whether it is running on the primary.
        Hope this helps.
        Thanks again!

        Reply

    • Well, the Microsoft script is a bit of a one-off. My solution is aimed at being a continuously running sync, so your AG replicas’ logins are always in sync if and when you need to fail over (planned or not).

      Reply

  18. Congratulations for this code.

    I had problems with the policy = OFF.

    So, I altered part of your code, to fix the problem:

    SELECT N’CREATE LOGIN [‘+sp.[name]+’] WITH PASSWORD=0x’+
    CONVERT(nvarchar(max), l.password_hash, 2)+N’ HASHED, CHECK_POLICY=’+
    CASE
    WHEN l.is_policy_checked = 1
    THEN ‘ON, SID=0x’+CONVERT(nvarchar(max), sp.[sid], 2)+’;’
    ELSE ‘OFF, SID=0x’+CONVERT(nvarchar(max), sp.[sid], 2)+’;’
    END IsPolicyCheck
    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;

    Now, this code get dynamically this configuration.

    Reply

  19. Daniel thank you for this, I have been testing this on a SQL 2014 AG and noticed that it didn’t seem to clone the server roles in my environment. I went back to check them on the primary node with you code – SELECT N’CREATE SERVER ROLE [‘+sp.[name]+N’];’
    FROM sys.server_principals AS sp
    WHERE sp.principal_id>=100 AND
    sp.[type]=’R’;
    But it returns no results, select * FROM sys.server_principals shows no rows with type ‘R’

    What am I doing wrong here?

    Thanks

    Reply

    • Only thing I can think of is if those server roles are default server roles (like sysadmin, public, etc). Those should obviously already exist at the secondary, hence the “WHERE principal_id>=100”. Do you actually have server roles that you’ve created yourself, with a principal_id=100″ to something like “owning_principal_id>1”?

      Let me know how that works out!

      Reply

    • Ah, I read your question again and realized that if you get no rows from sys.server_principals at all (WHERE type=’R’), maybe you don’t have sufficient permissions to view the server roles?

      Reply

  20. Like @mateorose I was also getting the error about the ##MS_PolicyEventProcessingLogin## already existing. This was not due to the policy being enabled as I had disabled it on both servers. I ended up just excluding it as shown below.

    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
    UPPER(sp.[name]) NOT LIKE ”NT SERVICE\%” AND
    sp.[name] NOT IN (”NT AUTHORITY\SYSTEM”, ”##MS_PolicyEventProcessingLogin##”)’;

    Reply

  21. Just as a side note, you can create your linked servers as the listener name so you don’t have to have multiple linked servers in scenarios where you have multiple secondaries. You just create it like any normal SQL Server based linked server (and we use Logins Current Security Context), but just make sure that you manually setup SPN’s for the listener name.

    For example, if your listener is “LSNR-Example” you would do the following at a command prompt:
    setspn -S MSSQLSvc/LSNR-Example.domain.com gmsa-ServiceAccount
    setspn -S MSSQLSvc/LSNR-Example.domain.com:1433 gmsa-ServiceAccount
    setspn -S MSSQLSvc/LSNR-Example.domain.com:Instance gmsa-ServiceAccount
    setspn -S MSSQLSvc/LSNR-Example.domain.com:50456 gmsa-ServiceAccount

    The above is an example of default instances and a named instance in case you have a secondary on a named instance (just make sure your port on the named instance is static and the Browser service is turned on).

    Reply

  22. Hi Daniel
    I have used this a few times and its great. I have made a couple of small changes to work around a few issues
    1) separated a password change from the other login changes and added CHECK_POLICY=OFF to this. CHECK_POLICY will be turned back on again with subsequent runs but this gets around errors for a hashed password change on a login with CHECK_POLICY turned on
    2) put the “revoke” items before the drop login. This prevents errors when a login is dropped and then roles cant be revoked from a login that has already been dropped. Not a big problem but its nice to have the procedure complete without errors and that way I can use the job notification “when the job fails”
    Thanks again
    Chris

    Reply

  24. Daniel Hi,

    Its very useful scripts.
    Logins are getting replicated to secondary but Roles are not.
    Can you please assist, Thanks in advance

    Reply

  25. Hi Daniel, fantastic script, however I am getting an error using the @exclude_logins, the rest of it works perfectly. Can’t for the life of me see if there is a syntax error when I run the proc

    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near ‘sp’.

    any ideas?

    Thanks
    Col

    Reply

    • Hi Colin,

      I don’t currently have a good test environment with AGs, so this was a bug that I hadn’t found in testing. I’ve pushed an update to the script, so it should work now.

      Daniel

      Reply

  27. Hi Daniel – thanks for the script.
    We have a client site using this script, and it generated this (which causes an error) –
    ALTER LOGIN [sa] WITH PASSWORD=…… HASHED, CHECK_POLICY=OFF, CHECK_POLICY=OFF;
    I believe this can occur from this code snippet –
    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, CHECK_POLICY=OFF’
    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);

    I’m curious if anyone else has found this issue?
    There’s a simple fix by unchecking “Enforce password policy” for the ‘sa’ logon on the secondary. Although it does look to me like there’s a bug in the code.

    Reply

  29. Thanks, the fix on Github worked well. It looks like there may be an issue if logins are enabled on one server and disabled on the secondary. Should the script cater for this, or would I need to ensure logins are enabled on secondaries (if enabled on the primary)?

    Reply

  30. Hi Daniel – Great script but I am unable to get it to work via a SQL Agent job, is this a known issue?

    Reply

  31. Pingback: Backup Sql Logins - login link

  32. Hi Daniel,

    thanks for your handy solution…it works almost perfect for me :D
    Maybe you can help me with a small problem regarding user drops.

    Whenever I delete a user on my primary replica and my secondary is synching I get the following error:
    Cannot drop the server principal ‘[some_user]’, because it does not exist or you do not have permission.

    That is bacause the drop first drops the login and after that the associated server role.
    DROP LOGIN [some_user];
    ALTER SERVER ROLE [processadmin] DROP MEMBER [some_user];

    Because the login is already gone it abviously cannot alter the role afterwards.
    What am i missing here?

    The query in my SQL job looks like follows:
    EXECUTE dbo.SyncLogins
    @primary_replica = ‘[TotallySecretSQLServername]\[TotallySecretInstanceName]’,
    @allow_drop_logins = 1,
    @check_policy = 1,
    @exclude_logins = ‘some_users’

    Thank you in advance for your reply.

    Regard
    Dennis

    Reply

  34. Pingback: Syncing Server Objects across AG nodes

Leave a reply to CA Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.