Scripting SQL Logins, Windows Logins, group membership

I’m working with AlwaysOn Availability Groups in SQ’L 2014. One of the many interesting gotchas I have with AlwaysOn is that SQL Logins on each AG node must be created with identical SIDs, otherwise after failover the database users for those SQL Logins will be orphaned.

I discovered Bill Graziano’s post back from 2010 where he scripts out the SQL Logins (including SID and hashed password), plus the Windows logins and server role membership for SQL 2008 server. I have adapted it for SQL 2014 SP2.

Note the script was originally designed to script out the logins only if they do not exist already. I removed the ‘IF NOT EXISTS’ lines so it will script out all the SQL Logins and Windows Logins regardless of whether they exist.

USE [master]
GO

/* Object: UserDefinedFunction [dbo].[fn_hexadecimal] **/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[fn_hexadecimal] 
(
 -- Add the parameters for the function here
 @binvalue varbinary(256)
)
RETURNS VARCHAR(256)
AS
BEGIN

 DECLARE @charvalue varchar(256)
 DECLARE @i int
 DECLARE @length int
 DECLARE @hexstring char(16)
 SELECT @charvalue = '0x'
 SELECT @i = 1
 SELECT @length = DATALENGTH (@binvalue)
 SELECT @hexstring = '0123456789ABCDEF' 
 WHILE (@i <= @length) 
 BEGIN
 DECLARE @tempint int
 DECLARE @firstint int
 DECLARE @secondint int
 SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
 SELECT @firstint = FLOOR(@tempint/16)
 SELECT @secondint = @tempint - (@firstint)
 SELECT @charvalue = @charvalue +
 SUBSTRING(@hexstring, @firstint+1, 1) +
 SUBSTRING(@hexstring, @secondint+1, 1)
 SELECT @i = @i + 1
 END
 return @charvalue

END
GO


SET NOCOUNT ON
GO
--use MASTER
GO
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Script created on ' + CAST(GETDATE() AS VARCHAR(100))
PRINT '-----------------------------------------------------------------------------'
PRINT ''
PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the windows logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULTDATABASE=[' + 
 default_database_name + '], DEFAULTLANGUAGE=[usenglish]
GO

'
FROM master.sys.server_principals
where type_desc In ('WINDOWS_GROUP', 'WINDOWS_LOGIN')
AND [name] not like 'BUILTIN%'
and [NAME] not like 'NT AUTHORITY%'
and [name] not like '%\SQLServer%'
GO

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Create the SQL Logins'
PRINT '-----------------------------------------------------------------------------'
select 'CREATE LOGIN [' + [name] + '] 
 WITH PASSWORD=' + [dbo].[fn_hexadecimal](password_hash) + ' HASHED,
 SID = ' + [dbo].[fn_hexadecimal](sid) + ', 
 DEFAULTDATABASE=[' + default_database_name + '], DEFAULTLANGUAGE=[usenglish], 
 CHECKEXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')
 ALTER LOGIN [' + [name] + ']
 WITH CHECKEXPIRATION=' + 
 CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=' + 
 CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'
--[name], [sid] , passwordhash 
from master.sys.sql_logins 
where type_desc = 'SQL_LOGIN' 
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
' 
from master.sys.server_principals 
where is_disabled = 1

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Assign groups'
PRINT '-----------------------------------------------------------------------------'
select 
'EXEC master..spaddsrvrolemember @loginame = N''' + l.name + ''', @rolename = N''' + r.name + '''
GO

'
from master.sys.server_role_members rm
join master.sys.server_principals r on r.principal_id = rm.role_principal_id
join master.sys.server_principals l on l.principal_id = rm.member_principal_id
where l.[name] not in ('sa')
AND l.[name] not like 'BUILTIN%'
and l.[NAME] not like 'NT AUTHORITY%'
and l.[name] not like '%\SQLServer%'

Leave a Reply

Your email address will not be published. Required fields are marked *