Menu
–By Lori Brown @SQLSupahStah
At one of my clients who has an AlwaysOn failover cluster, I noticed as I was checking up on things on the secondary replica that there were several fewer Windows and SQL logins on the secondary than on the primary. I really did not want to tediously compare the logins on each so instead came up with a script that can be run periodically to script out any logins that need to be synched up between the replicas. I actually found most of the script that I needed on the SQLSoldier’s blog (http://www.sqlsoldier.com/wp/) in a stored procedure that was intended to synch logins between mirrored partners. Since AlwaysOn is Mirroring on steroids I thought that it would work and it did. I did put my own touches on the proc by turning it into a standalone script that uses OPENROWSET to query the AG primary replica and also changed it from automatically executing the statements for missing logins to simply printing them into a script that can be applied as needed.
And now….here is what you came here for….
— Original logic from http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
—
— Sync Logins to AlwaysOn Replicas
— Inputs: @PartnerServer – Target Instance (InstName or Machine\NamedInst or Instname,port)
— Output: All Statements to create logins with SID and Password for both Windows and SQL logins
— Will also add logins to server roles
—
— Person executing this must be sysadmin
— Ad Hoc Distributed Queries must be enabled for this to work without setting up a linked server
/*
— Turn on Ad Hoc Distributed Queries so we don’t have to set up a linked server
sp_configure ‘show advanced options’, 1
GO
reconfigure with override
go
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
reconfigure with override
go
*/
Use master;
Go
Declare @MaxID int,
@CurrID int,
@PartnerServer sysname,
@SQL nvarchar(max),
@LoginName sysname,
@IsDisabled int,
@Type char(1),
@SID varbinary(85),
@SIDString nvarchar(100),
@PasswordHash varbinary(256),
@PasswordHashString nvarchar(300),
@RoleName sysname,
@Machine sysname,
@PermState nvarchar(60),
@PermName sysname,
@Class tinyint,
@MajorID int,
@ErrNumber int,
@ErrSeverity int,
@ErrState int,
@ErrProcedure sysname,
@ErrLine int,
@ErrMsg nvarchar(2048)
SET @PartnerServer = ‘InstName’
Declare @Logins Table (LoginID int identity(1, 1) not null primary key,
[Name] sysname not null, [SID] varbinary(85) not null,IsDisabled int not null,
[Type] char(1) not null,PasswordHash varbinary(256) null)
Declare @Roles Table (RoleID int identity(1, 1) not null primary key,
RoleName sysname not null,
LoginName sysname not null)
Declare @Perms Table (PermID int identity(1, 1) not null primary key,
LoginName sysname not null,
PermState nvarchar(60) not null,
PermName sysname not null,
Class tinyint not null,
ClassDesc nvarchar(60) not null,
MajorID int not null,
SubLoginName sysname null,
SubEndPointName sysname null)
Set NoCount On;
If CharIndex(‘\’, @PartnerServer) > 0 — Check for Named Instance
Begin
Set @Machine = LEFT(@PartnerServer, CharIndex(‘\’, @PartnerServer) – 1);
End
Else If CharIndex(‘,’, @PartnerServer) > 0 — Check for Instance with port in connection string
Begin
Set @Machine = LEFT(@PartnerServer, CharIndex(‘,’, @PartnerServer) – 1);
End
Else
Begin
Set @Machine = @PartnerServer;
End
— Get all Windows logins from principal server
— using OPENROWSET and Windows Authentication
Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name, P.sid, P.is_disabled, P.type, L.password_hash
From master.sys.server_principals P
Left Join master.sys.sql_logins L On L.principal_id = P.principal_id
Where P.type In (””U””, ””G””, ””S””)
And P.name <> ””sa””
And P.name Not Like ””##%””
And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’
–print @SQL
Insert Into @Logins (Name, SID, IsDisabled, Type, PasswordHash)
Exec sp_executesql @SQL;
— Get all roles from principal server
— using OPENROWSET and Windows Authentication
Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select RoleP.name as RoleName, LoginP.name as LoginName
From master.sys.server_role_members RM
Inner Join master.sys.server_principals RoleP
On RoleP.principal_id = RM.role_principal_id
Inner Join master.sys.server_principals LoginP
On LoginP.principal_id = RM.member_principal_id
Where LoginP.type In (””U””, ””G””, ””S””)
And LoginP.name <> ””sa””
And LoginP.name Not Like ””##%””
And RoleP.type = ””R””
And CharIndex(””’ + @Machine + ‘\””, LoginP.name) = 0”)as a;’
–print @SQL
Insert Into @Roles (RoleName, LoginName)
Exec sp_executesql @SQL;
— Get all explicitly granted permissions
— using OPENROWSET and Windows Authentication
Set @SQL = ‘Select a.* From OPENROWSET (”SQLNCLI”, ”Server=’+@PartnerServer+‘;Trusted_Connection=yes;”, ”Select P.name Collate database_default,
SP.state_desc, SP.permission_name, SP.class, SP.class_desc, SP.major_id,
SubP.name Collate database_default,
SubEP.name Collate database_default
From master.sys.server_principals P
Inner Join master.sys.server_permissions SP
On SP.grantee_principal_id = P.principal_id
Left Join master.sys.server_principals SubP
On SubP.principal_id = SP.major_id And SP.class = 101
Left Join master.sys.endpoints SubEP
On SubEP.endpoint_id = SP.major_id And SP.class = 105
Where P.type In (””U””, ””G””, ””S””)
And P.name <> ””sa””
And P.name Not Like ””##%””
And CharIndex(””’ + @Machine + ‘\””, P.name) = 0”)as a;’
–print @SQL
Insert Into @Perms (LoginName, PermState, PermName, Class, ClassDesc, MajorID, SubLoginName, SubEndPointName)
Exec sp_executesql @SQL;
Select @MaxID = Max(LoginID), @CurrID = 1
From @Logins;
While @CurrID <= @MaxID
Begin
Select @LoginName = Name,
@IsDisabled = IsDisabled,
@Type = [Type],
@SID = [SID],
@PasswordHash = PasswordHash
From @Logins
Where LoginID = @CurrID;
If Not Exists (Select 1 From sys.server_principals
Where name = @LoginName)
Begin
Set @SQL = ‘Create Login ‘ + quotename(@LoginName)
If @Type In (‘U’, ‘G’)
Begin
Set @SQL = @SQL + ‘ From Windows;’
End
Else
Begin
Set @PasswordHashString = ‘0x’ +
Cast(” As XML).value(‘xs:hexBinary(sql:variable(“@PasswordHash”))’, ‘nvarchar(300)’);
Set @SQL = @SQL + ‘ With Password = ‘ + @PasswordHashString + ‘ HASHED, ‘;
Set @SIDString = ‘0x’ +
Cast(” As XML).value(‘xs:hexBinary(sql:variable(“@SID”))’, ‘nvarchar(100)’);
Set @SQL = @SQL + ‘SID = ‘ + @SIDString + ‘;’;
End
Print @SQL;
If @IsDisabled = 1
Begin
Set @SQL = ‘Alter Login ‘ + quotename(@LoginName) + ‘ Disable;’
Print @SQL;
End
End
Set @CurrID = @CurrID + 1;
End
Select @MaxID = Max(RoleID), @CurrID = 1
From @Roles;
While @CurrID <= @MaxID
Begin
Select @LoginName = LoginName,
@RoleName = RoleName
From @Roles
Where RoleID = @CurrID;
If Not Exists (Select 1 From sys.server_role_members RM
Inner Join sys.server_principals RoleP
On RoleP.principal_id = RM.role_principal_id
Inner Join sys.server_principals LoginP
On LoginP.principal_id = RM.member_principal_id
Where LoginP.type In (‘U’, ‘G’, ‘S’)
And RoleP.type = ‘R’
And RoleP.name = @RoleName
And LoginP.name = @LoginName)
Begin
Print ‘Exec sp_addsrvrolemember @rolename = ”’ + @RoleName + ”’, @loginame = ”’ + @LoginName + ”’;’;
End
Set @CurrID = @CurrID + 1;
End
Select @MaxID = Max(PermID), @CurrID = 1
From @Perms;
While @CurrID <= @MaxID
Begin
Select @PermState = PermState,
@PermName = PermName,
@Class = Class,
@LoginName = LoginName,
@MajorID = MajorID,
@SQL = PermState + space(1) + PermName + SPACE(1) +
Case Class When 101 Then ‘On Login::’ + QUOTENAME(SubLoginName)
When 105 Then ‘On ‘ + ClassDesc + ‘::’ + QUOTENAME(SubEndPointName)
Else ” End +
‘ To ‘ + QUOTENAME(LoginName) + ‘;’
From @Perms
Where PermID = @CurrID;
If Not Exists (Select 1 From sys.server_principals P
Inner Join sys.server_permissions SP On SP.grantee_principal_id = P.principal_id
Where SP.state_desc = @PermState
And SP.permission_name = @PermName
And SP.class = @Class
And P.name = @LoginName
And SP.major_id = @MajorID)
Begin
Print @SQL;
End
Set @CurrID = @CurrID + 1;
End
Set NoCount Off;
GO
/*
— Turn off Ad Hoc Distributed Queries
sp_configure ‘Ad Hoc Distributed Queries’, 0
GO
reconfigure with override
go
*/
On my test AG replicas, you can see that there are two users that are not present on the secondary. I need to add them so that if a failover occurs then there will not be any login failures.
Using the script above on SQLSRVTST2, I get the following output and can use that to create my users with their correct permissions. I had to set the @PartnerServer variable to SQLSRVTST1 in the script and enable Ad Hoc Distributed Queries.
……
The nice thing is that the password and SID’s are also scripted for you. There is no need to install sp_hexadecimal and sp_help_revlogin in master. If you have AG replicas with a lot of logins, this can be a real timesaver for you. Hope you enjoy and if you make any improvements, please share them with us!
For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!