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 server 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)ASBEGIN

    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.server_principals WHERE [name] = ''' + [name] + ''')  
    CREATE LOGIN [' + [name] + '] FROM WINDOWS WITH DEFAULT_DATABASE=[' + 
        default_database_name + '], DEFAULT_LANGUAGE=[us_english]
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 'IF NOT EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')  
    CREATE LOGIN [' + [name] + '] 
        WITH PASSWORD=' + [master].[dbo].[fn_hexadecimal](password_hash) + ' HASHED,
        SID = ' + [master].[dbo].[fn_hexadecimal]([sid]) + ',  
        DEFAULT_DATABASE=[' + default_database_name + '], DEFAULT_LANGUAGE=[us_english], 
        CHECK_EXPIRATION=' + CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=OFF
GO  
IF EXISTS (SELECT * FROM master.sys.sql_logins WHERE [name] = ''' + [name] + ''')  
    ALTER LOGIN [' + [name] + ']
        WITH CHECK_EXPIRATION=' + 
            CASE WHEN is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + 
            CASE WHEN is_policy_checked = 1 THEN 'ON' ELSE 'OFF' END + '
GO


'  
--[name], [sid] , password_hash 
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..sp_addsrvrolemember @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%'