SQL Server 2012 AlwaysOn - User Management

Continuing on from my last post about AlwaysOn..

User Management and maintaining was of critical importance in the scenario we found ourselves in, our systems relied mostly on SQL logins.

Always on databases maintain users exactly as expected, however, as each replica is an independent SQL server creating a SQL login on one replica does not create it on the others.

It was decided that our creation process should be altered to create every login on each replica at creation, but we also knew there was a danger that one would be missed or would be created whilst a replica was down for updates etc etc

So once again we had to weigh up the cost/risk of how often it should be checked regularly, this is the basis for yet another job that runs on each replica once an hour, they are offset on each box also so on the hour, 10 past, 20 past etc etc

We have been careful to ensure that SIDs are also copied over so that logins get linked to database users correctly. We also use the password hash in the creation so that it is easily transferable.


Basically add an extra cursor chunk for each replica, the below example assumes 2 replicas only

CREATE PROCEDURE [dbo].[Agsp_getnewusers] 
AS 
    DECLARE @name VARCHAR( 150 ); 
    DECLARE @ssql VARCHAR( 2500 ); 
    DECLARE @sid VARCHAR( 250 ); 
    DECLARE @pass VARCHAR( 250 ); 

    IF( @@SERVERNAME = '<REPLICA1>' ) 
      BEGIN 
          DECLARE users CURSOR FOR 
            SELECT [name], 
                   CONVERT(VARCHAR( 200 ), sid, 2), 
                   CONVERT(VARCHAR( 400 ), [password_hash], 2) 
            FROM   [].master.sys.sql_logins 
            WHERE  NAME + 'CHECK' NOT IN(SELECT [name] + 'CHECK' 
                                         FROM   master.sys.sql_logins); 

          OPEN users; 

          FETCH next FROM users INTO @name, @sid, @pass; 

          WHILE( @@FETCH_STATUS = 0 ) 
            BEGIN 
                SET @ssql = 'CREATE LOGIN [' + @name 
                            + '] WITH PASSWORD=0x' + @pass 
                            + ' hashed, sid=0x' + @sid 
                            + 
      ', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'; 

      EXEC Sp_executesql 
        @ssql; 

      FETCH next FROM users INTO @name, @sid, @pass; 
            END; 

          CLOSE users; 

          DEALLOCATE users; 
      END; 

    IF( @@SERVERNAME = '<REPLICA2>' ) 
      BEGIN 
          DECLARE users CURSOR FOR 
            SELECT [name], 
                   CONVERT(VARCHAR( 200 ), sid, 2), 
                   CONVERT(VARCHAR( 400 ), [password_hash], 2) 
            FROM   [].master.sys.sql_logins 
            WHERE  NAME + 'CHECK' NOT IN(SELECT [name] + 'CHECK' 
                               FROM   master.sys.sql_logins); 

          OPEN users; 

          FETCH next FROM users INTO @name, @sid, @pass; 

          WHILE( @@FETCH_STATUS = 0 ) 
            BEGIN 
                SET @ssql = 'CREATE LOGIN [' + @name 
                            + '] WITH PASSWORD=0x' + @pass 
                            + ' hashed, sid=0x' + @sid 
                            + 
      ', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF'; 

      PRINT @ssql; 

      EXEC Sp_executesql 
        @ssql; 

      FETCH next FROM users INTO @name, @sid, @pass; 
            END; 

          CLOSE users; 

          DEALLOCATE users; 
      END; 



You may have noticed the +'CHECK' on the name, this was a bit of a fudge as I created a few test users whilst testing this method of Testuser1,Testuser2,Testuser3 and noticed that for some reason Testuser2 would not come over to replica1 where I had created TestUser1, so  I'm wondering if this is an odd comparison issue with strings ending in numbers, anyone know??