Script SQL Server Logins in SQL server 2005

This article describes how to transfer the logins and passwords between different instances of Microsoft SQL Server.The instances may be on the same server or on different servers, and their versions may differ.In case of SQL Server disaster recovery, we need to make sure that we can recover the logins onto another SQL Server instance. 

Once you execute below code, you'll have two new stored procedures in your master database. One (sp_hexadecimal)  is used to translate the password hash into text form and the other (sp_script_login) is actually extracts the login with the appropriate information 

To get Scripts of login of user's, execute as below.

    exec sp_script_login

Commands:

USE master
GO

IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO

CREATE PROCEDURE dbo.sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS

DECLARE @charvalue varchar (514)
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

SELECT @hexvalue = @charvalue
GO

USE [master]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

IF OBJECT_ID ('sp_script_login') IS NOT NULL
DROP PROCEDURE sp_script_login
GO

CREATE PROCEDURE dbo.sp_script_login
@login_name SYSNAME = NULL
, @DropExisting BIT = 0
AS
SET nocount ON
DECLARE
@name    SYSNAME
, @xstatus INT
, @binpwd  VARBINARY (256)
, @txtpwd  SYSNAME
, @OutputString  VARCHAR (256)
, @SID_varbinary VARBINARY(85)
, @SID_string VARCHAR(256)
, @isntgroup BIT, @isntuser BIT, @sysadmin BIT, @securityadmin BIT, @serveradmin BIT, @setupadmin BIT, @processadmin BIT, @diskadmin BIT, @dbcreator BIT
, @bulkadmin BIT, @denylogin BIT

--DECLARE @SvrPerms TABLE (
-- Permission VARCHAR(20)
--, xstatus INT )
--
--INSERT @SvrPerms
--SELECT 'sysadmin' , 16
--UNION ALL
--SELECT 'securityadmin' , 32
--UNION ALL
--SELECT  'serveradmin' , 64
--UNION ALL
--SELECT  'setupadmin' , 128
--UNION ALL
--SELECT  'processadmin' , 256
--UNION ALL
--SELECT  'diskadmin' , 512
--UNION ALL
--SELECT  'dbcreator' , 1024
--UNION ALL
--SELECT  'bulkadmin' , 4096

IF (@login_name IS NULL)
BEGIN
--PRINT 'Null Login'
--DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name <> 'sa'
DECLARE login_curs CURSOR FOR
SELECT l.sid, l.name, CAST( LOGINPROPERTY( l.name, 'PasswordHash' ) AS VARBINARY (256)) AS binpassword,
isntgroup, isntuser, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin, denylogin
FROM sys.syslogins l
INNER JOIN sys.server_principals r
ON l.sid = r.sid
WHERE type IN('U', 'G', 'S') AND is_disabled = 0 AND l.name <> 'sa'
END

ELSE
BEGIN
--DECLARE login_curs CURSOR FOR SELECT sid, name, xstatus, password FROM master..sysxlogins WHERE srvid IS NULL AND name = @login_name
DECLARE login_curs CURSOR FOR
SELECT l.sid, l.name, CAST( LOGINPROPERTY( l.name, 'PasswordHash' ) AS VARBINARY (256)) AS binpassword,
isntgroup, isntuser, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin, denylogin
FROM sys.syslogins l
INNER JOIN sys.server_principals r
ON l.sid = r.sid
WHERE type IN('U', 'G', 'S') AND is_disabled = 0 AND l.name <> 'sa'
END

OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @binpwd, @isntgroup, @isntuser, @sysadmin, @securityadmin, @serveradmin, @setupadmin, @processadmin,
@diskadmin, @dbcreator, @bulkadmin, @denylogin

SET @OutputString = '/* sp_script_login script '
PRINT @OutputString
SET @OutputString = '** Generated '  + CONVERT (VARCHAR, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @OutputString
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status = 0)
BEGIN
PRINT ''
SET @OutputString = '-- Login: ' + @name
PRINT @OutputString
SET @OutputString = 'IF not exists(select 1 from master.dbo.syslogins where name='+CHAR(39)+@name+CHAR(39)+' and '+CAST(@DropExisting AS VARCHAR)+'=0)'
PRINT @OutputString
SET @OutputString = 'begin'
PRINT @OutputString

-- NT authenticated account/group
--IF (@xstatus & 4) = 4
IF @isntgroup = 1 OR @isntuser = 1
BEGIN
--IF (@xstatus & 1) = 1
IF @denylogin = 1
BEGIN -- NT login is denied access
SET @OutputString = CHAR(9)+'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @OutputString
END
ELSE
BEGIN -- NT login has access
SET @OutputString = CHAR(9)+'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @OutputString
END

SET @OutputString=''

--  select @OutputString =char(9)+'EXEC sp_addsrvrolemember '+char(39)+@name+char(39)+','+char(39)+p.Permission+char(39)
--  from sysxlogins l
--  join @SvrPerms p on p.xstatus&l.xstatus=p.xstatus
--  where l.name=@name

IF @sysadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'sysadmin' + CHAR(39)

PRINT @OutputString
END

IF @securityadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'securityadmin' + CHAR(39)

PRINT @OutputString
END  

IF @serveradmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'serveradmin' + CHAR(39)

PRINT @OutputString
END   

IF @setupadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'setupadmin' + CHAR(39)

PRINT @OutputString
END 

IF @processadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'processadmin' + CHAR(39)

PRINT @OutputString
END 

IF @diskadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'diskadmin' + CHAR(39)

PRINT @OutputString
END 

IF @dbcreator = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'dbcreator' + CHAR(39)

PRINT @OutputString
END 

IF @bulkadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'bulkadmin' + CHAR(39)

PRINT @OutputString
END 

END

-- SQL Server authentication
ELSE
BEGIN
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
--  IF (@xstatus & 2048) = 2048 -- Upgraed from SS 6.5???
--  begin
--   SET @OutputString = char(9)+'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
--  end
--  ELSE
--  begin
--   SET @OutputString = char(9)+'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
--  END

SET @OutputString = CHAR(9)+'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'

PRINT @OutputString
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @OutputString = CHAR(9)+'if '+CAST(@DropExisting AS VARCHAR)+'=1'
PRINT @OutputString
SET @OutputString = CHAR(9)+'begin'
PRINT @OutputString
SET @OutputString = CHAR(9)+CHAR(9)+'exec sp_droplogin '+CHAR(39)+@name+CHAR(39)
PRINT @OutputString
SET @OutputString = CHAR(9)+'end'
PRINT @OutputString
SELECT @OutputString = CHAR(9)+'EXEC master..sp_addlogin '+CHAR(39) + @name +CHAR(39)+', '+CASE WHEN @binpwd IS NULL THEN 'NULL' ELSE '@pwd' END+', @sid = ' + @SID_string + ', @encryptopt = '+CHAR(39)+'skip_encryption'+CHAR(39)

PRINT @OutputString

--  set @OutputString=''
--  select @OutputString =char(9)+'EXEC sp_addsrvrolemember '+char(39)+@name+char(39)+','+char(39)+p.Permission+char(39)
--  from sysxlogins l
--  join @SvrPerms p on p.xstatus&l.xstatus=p.xstatus
--  where l.name=@name

IF @sysadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'sysadmin' + CHAR(39)

PRINT @OutputString
END

IF @securityadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'securityadmin' + CHAR(39)

PRINT @OutputString
END  

IF @serveradmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'serveradmin' + CHAR(39)

PRINT @OutputString
END   

IF @setupadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'setupadmin' + CHAR(39)

PRINT @OutputString
END 

IF @processadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'processadmin' + CHAR(39)

PRINT @OutputString
END 

IF @diskadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'diskadmin' + CHAR(39)

PRINT @OutputString
END 

IF @dbcreator = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'dbcreator' + CHAR(39)

PRINT @OutputString
END 

IF @bulkadmin = 1
BEGIN
SET @OutputString = CHAR(9)+'EXEC sp_addsrvrolemember '+ CHAR(39)+@name+CHAR(39)+','+CHAR(39)+ 'bulkadmin' + CHAR(39)

PRINT @OutputString
END 

END
PRINT 'END'
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @binpwd, @isntgroup, @isntuser, @sysadmin, @securityadmin, @serveradmin, @setupadmin, @processadmin,
@diskadmin, @dbcreator, @bulkadmin, @denylogin
END

CLOSE login_curs
DEALLOCATE login_curs

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON

GO

OUTPUT:-

More:

➤ Login Scripts for SQL SERVER 2005
➤ Login Scripts for SQL SERVER 2008 & above