Scripting out SQL Server Logins

I regularly move logins between servers.  Mostly this is between production and our DR site.  I’ve used the code in KB246133 many, many times but it’s pretty limited.  I started with that and wrote the script below.

  • There is a user-defined function that’s created in master.  You can create it in any database you want but you’ll need to update the script.  The function converts varbinary hashed passwords to a string representation.
  • It keeps the password intact for SQL Server logins.
  • It scripts both Windows logins and SQL Server logins.  It also scripts role membership.
  • It keeps the SID intact for SQL Server logins.  This is important so you don’t have to remap users to logins.
  • The script that is generated uses IF NOT EXISTS so that it doesn’t try to create logins that already exist.
  • It DOES NOT handle removal of logins from roles.  It does handle disabled accounts but I haven’t done much testing on that.
  • I’ve tested this on SQL Server 2005 and SQL Server 2008.
  • You’ll probably need to change your results so that you display more characters by default.  Under Tools –> Options –> Query Results –> SQL Server –> Results to Text increase the maximum number of characters returned to 8192 (or a number high enough that the results aren’t truncated).  You’ll want to set results to text before running this.
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
16)
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 'IF NOT EXISTS (SELECT * FROM master.sys.serverprincipals WHERE [name] = ''' + [name] + ''')
CREATE LOGIN ['
+ [name] + '] FROM WINDOWS WITH DEFAULT
DATABASE=[' +
defaultdatabasename + '], DEFAULTLANGUAGE=[usenglish]
GO

'

FROM master.sys.serverprincipals
where type
desc In ('WINDOWSGROUP', 'WINDOWSLOGIN')
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 'IF NOT EXISTS (SELECT * FROM master.sys.sqllogins WHERE [name] = ''' + [name] + ''')
CREATE LOGIN ['
+ [name] + ']
WITH PASSWORD='
+ [master].[dbo].fn
hexadecimal + ' HASHED,
SID = '
+ [master].[dbo].fnhexadecimal + ',
DEFAULT
DATABASE=[' + defaultdatabasename + '], DEFAULTLANGUAGE=[usenglish],
CHECKEXPIRATION='
+ CASE WHEN isexpirationchecked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=OFF
GO
IF EXISTS (SELECT * FROM master.sys.sqllogins WHERE [name] = ''' + [name] + ''')
ALTER LOGIN ['
+ [name] + ']
WITH CHECK
EXPIRATION=' +
CASE WHEN isexpirationchecked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECKPOLICY=' +
CASE WHEN is
policychecked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'

--[name], [sid] , password
hash
from master.sys.sqllogins
where type
desc = 'SQLLOGIN'
and [name] not in ('sa', 'guest')

PRINT '-----------------------------------------------------------------------------'
PRINT '-- Disable any logins'
PRINT '-----------------------------------------------------------------------------'
SELECT 'ALTER LOGIN [' + [name] + '] DISABLE
GO
'

from master.sys.server
principals
where isdisabled = 1

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

'

from master.sys.serverrolemembers rm
join master.sys.serverprincipals r on r.principalid = rm.roleprincipalid
join master.sys.serverprincipals l on l.principalid = rm.memberprincipalid
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%'