Extracting Logins From SQL Server

/*******************************************************
* SQL 2005 Extract Logins 
* Hugh Scott
* 2010/01/06
*
* Description:
* Modified from the original to remove dependency on sp_VarbinaryToHex
* stored procedure. Other minor modifications as well.
*
* Modification History:
* Date            Initials    Modification
* 2010/01/06    HMS            - Original
*
*********************************************************/use master

DECLARE @login_name sysname 

SET @login_name = null

DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
 DECLARE login_curs CURSOR FOR

 SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
 ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
 DECLARE login_curs SCROLL CURSOR FOR


 SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM 
sys.server_principals p LEFT JOIN sys.syslogins l
 ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
 PRINT 'No login(s) found.'
 CLOSE login_curs
 DEALLOCATE login_curs
 -- RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 PRINT ''
 SET @tmpstr = '-- Login: ' + @name
 PRINT @tmpstr
 IF (@type IN ( 'G', 'U'))
 BEGIN -- NT authenticated account/group

 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
 END
 ELSE BEGIN -- SQL Server authentication
 -- obtain password and sid
 SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
 -- EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
 SELECT @PWD_string = master.dbo.fn_varbintohexstr(@PWD_varbinary)


 -- EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SELECT @SID_string = master.dbo.fn_varbintohexstr(@SID_varbinary)

 -- obtain password policy state
 SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
 SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

 SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

 IF ( @is_policy_checked IS NOT NULL )
 BEGIN
 SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
 END
 IF ( @is_expiration_checked IS NOT NULL )
 BEGIN
 SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
 END
 END
 IF (@denylogin = 1)
 BEGIN -- login is denied access
 SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
 END
 ELSE IF (@hasaccess = 0)
 BEGIN -- login exists but does not have access
 SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
 END
 IF (@is_disabled = 1)
 BEGIN -- login is disabled
 SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
 END
 PRINT @tmpstr
 END

 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
 END
CLOSE login_curs
DEALLOCATE login_curs
-- RETURN 0
GO



/*******************************************************
* SQL 2000 Extract Logins 
* Hugh Scott
* 2010/01/06
*
* Description:
* Modified from the original to remove dependency on sp_VarbinaryToHex
* stored procedure. Other minor modifications as well.
*
* Modification History:
* Date            Initials    Modification
* 2010/01/06    HMS            - Original
*
*********************************************************/DECLARE @login_name sysname
DECLARE @include_db bit 
DECLARE @include_role bit

SET @login_name = null
SET @include_db = 1
SET @include_role = 1

DECLARE @name sysname
DECLARE @xstatus int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
 DECLARE login_curs CURSOR FOR 
 SELECT sid, name, xstatus, password FROM master..sysxlogins 
 WHERE srvid IS NULL AND name <> 'sa'
ELSE
 DECLARE login_curs SCROLL CURSOR FOR 
 SELECT sid, name, xstatus, password FROM master..sysxlogins 
 WHERE srvid IS NULL AND name = @login_name
OPEN login_curs 
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
 PRINT 'No login(s) found.'
 CLOSE login_curs 
 DEALLOCATE login_curs 
 -- RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr
SET @tmpstr = '** Generated ' 
 + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
 PRINT ''
 SET @tmpstr = '-- Login: ' + @name
 PRINT @tmpstr 
 IF (@xstatus & 4) = 4
 BEGIN -- NT authenticated account/group
 IF (@xstatus & 1) = 1
 BEGIN -- NT login is denied access
 SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
 PRINT @tmpstr 
 END
 ELSE BEGIN -- NT login has access
 SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
 PRINT @tmpstr 
 END
 END
 ELSE BEGIN -- SQL Server authentication
 IF (@binpwd IS NOT NULL)
 BEGIN -- Non-null password
 -- EXEC sp_hexadecimal @binpwd, @txtpwd OUT
 SELECT @txtpwd = master.dbo.fn_varbintohexstr(@binpwd)
 IF (@xstatus & 2048) = 2048
 SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
 ELSE
 SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
 PRINT @tmpstr
    -- EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
    SELECT @SID_string = master.dbo.fn_varbintohexstr(@SID_varbinary)
 SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
 + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
 END
 ELSE BEGIN 
 -- Null password
    EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
 SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name 
 + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
 END
 IF (@xstatus & 2048) = 2048
 -- login upgraded from 6.5
 SET @tmpstr = @tmpstr + '''skip_encryption_old''' 
 ELSE 
 SET @tmpstr = @tmpstr + '''skip_encryption'''
 PRINT @tmpstr 
 END
 END
 FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
 END
CLOSE login_curs 
DEALLOCATE login_curs 
-- RETURN 0
GO


/*******************************************************
* SQL 2000 Extract Logins and Roles
* Hugh Scott
* 2010/01/06
*
* Description:
* Modified from the original to remove dependency on sp_VarbinaryToHex
* stored procedure. Other minor modifications as well.
*
* Modification History:
* Date            Initials    Modification
* 2010/01/06    HMS            - Original
*
*********************************************************/DECLARE @login_name sysname
DECLARE @include_db bit 
DECLARE @include_role bit

SET @login_name = null
SET @include_db = 1
SET @include_role = 1

DECLARE @name sysname 
DECLARE @xstatus int 
DECLARE @binpwd varbinary (256) 
DECLARE @dfltdb varchar (256) 
DECLARE @txtpwd sysname 
DECLARE @tmpstr varchar (256) 
DECLARE @SID_varbinary varbinary(85) 
DECLARE @SID_string varchar(256) 

IF (@login_name IS NULL) 
DECLARE login_curs CURSOR FOR 
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
FROM master.dbo.sysxlogins 
WHERE srvid IS NULL AND 
[name] <> 'sa' 
ELSE 
DECLARE login_curs SCROLL CURSOR FOR 
SELECT sid, [name], xstatus, password, isnull(db_name(dbid), 'master') 
FROM master.dbo.sysxlogins 
WHERE srvid IS NULL AND 
[name] = @login_name 

OPEN login_curs 

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 

IF (@@fetch_status = -1) 
BEGIN 
    PRINT 'No login(s) found.' 
    CLOSE login_curs 
    DEALLOCATE login_curs 

END 

SET @tmpstr = '/* sp_help_revlogin script ' 
PRINT @tmpstr 
SET @tmpstr = '** Generated ' 
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */' 
PRINT @tmpstr 
PRINT '' 
PRINT '' 
PRINT '' 
PRINT '/***** CREATE LOGINS *****/' 

WHILE @@fetch_status = 0 
BEGIN 
PRINT '' 
SET @tmpstr = '-- Login: ' + @name 
PRINT @tmpstr 

IF (@xstatus & 4) = 4 
BEGIN -- NT authenticated account/group 
IF (@xstatus & 1) = 1 
BEGIN -- NT login is denied access 
SET @tmpstr = '' --'EXEC master..sp_denylogin ''' + @name + '''' 
PRINT @tmpstr 
END 
ELSE 
BEGIN -- NT login has access 
SET @tmpstr = 'IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE [name] = ''' + @name + ''')' 
PRINT @tmpstr 
SET @tmpstr = CHAR(9) + 'CREATE LOGIN [' + @name + '] FROM WINDOWS' 
PRINT @tmpstr 
END 
END 
ELSE 
BEGIN -- SQL Server authentication 
-- EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT 
SELECT @SID_string = master.dbo.fn_varbintohexstr(@SID_varbinary)

IF (@binpwd IS NOT NULL) 
BEGIN -- Non-null password 
-- EXEC sp_hexadecimal @binpwd, @txtpwd OUT 
SELECT @txtpwd = master.dbo.fn_varbintohexstr(@binpwd)

SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=' + @txtpwd + ' HASHED' 
END 
ELSE 
BEGIN -- Null password 
SET @tmpstr = 'CREATE LOGIN [' + @name + '] WITH PASSWORD=''''' 
END 

SET @tmpstr = @tmpstr + ', CHECK_POLICY=OFF, SID=' + @SID_string 
PRINT @tmpstr 
END 

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
END 

IF @include_db = 1 
BEGIN 
PRINT '' 
PRINT '' 
PRINT '' 
PRINT '/***** SET DEFAULT DATABASES *****/' 

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 

WHILE @@fetch_status = 0 
BEGIN 
PRINT '' 
SET @tmpstr = '-- Login: ' + @name 
PRINT @tmpstr 

SET @tmpstr = 'ALTER LOGIN [' + @name + '] WITH DEFAULT_DATABASE=[' + @dfltdb + ']' 
PRINT @tmpstr 

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
END 
END 

IF @include_role = 1 
BEGIN 
PRINT '' 
PRINT '' 
PRINT '' 
PRINT '/***** SET SERVER ROLES *****/' 

FETCH FIRST FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 

WHILE @@fetch_status = 0 
BEGIN 
PRINT '' 
SET @tmpstr = '-- Login: ' + @name 
PRINT @tmpstr 

IF @xstatus &16 = 16 -- sysadmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''sysadmin''' 
PRINT @tmpstr 
END 

IF @xstatus &32 = 32 -- securityadmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''securityadmin''' 
PRINT @tmpstr 
END 

IF @xstatus &64 = 64 -- serveradmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''serveradmin''' 
PRINT @tmpstr 
END 

IF @xstatus &128 = 128 -- setupadmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''setupadmin''' 
PRINT @tmpstr 
END 

IF @xstatus &256 = 256 --processadmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''processadmin''' 
PRINT @tmpstr 
END 

IF @xstatus &512 = 512 -- diskadmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''diskadmin''' 
PRINT @tmpstr 
END 

IF @xstatus &1024 = 1024 -- dbcreator 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''dbcreator''' 
PRINT @tmpstr 
END 

IF @xstatus &4096 = 4096 -- bulkadmin 
BEGIN 
SET @tmpstr = 'exec master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''bulkadmin''' 
PRINT @tmpstr 
END 

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd, @dfltdb 
END 
END 

CLOSE login_curs 
DEALLOCATE login_curs