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%'