Original Post
https://community.dynamics.com/gp/f/32/p/61646/199961.aspx#.UdQ102LHIjg
/************************************************************************************************
*
* Purpose: To capture all the SQL Server logins with the binary password regardless of SQL version.
* Script is compatible with SQL 7 / SQL 2000 / SQL 2005. Need to save the results
* to a text file and run within a new query window.
*
* Created Date: Initial - 01/13/2006
*
* Revisions: 01/15/2006 - Made some formatting changes to the output text.
* 01/16/2006 - Made syntax change to account for Binary version of SQL Server.
* 01/16/2006 - Change version SQL version check because of syntax differences between
* SQL 2000 and 2005 with @@version.
* 04/10/2007 - Made change to deal with user names that begin with numbers instead of characters.
* 11/14/2008 - Made syntax change to update for SQL 2008
*
************************************************************************************************/
USE master
go
IF Object_id ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
go
CREATE PROCEDURE Sp_hexadecimal @binvalue VARBINARY(256),
@hexvalue VARCHAR(256) output
AS
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
SELECT @hexvalue = @charvalue
go
IF Object_id ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
go
CREATE PROCEDURE Sp_help_revlogin @login_name SYSNAME = NULL
AS
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 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
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
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
IF Object_id ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seemigratesqllogins
go
CREATE PROCEDURE Seemigratesqllogins @login_name SYSNAME = NULL
AS
DECLARE @name CHAR(50),
@binpwd VARBINARY (256),
@txtpwd SYSNAME,
@tmpstr VARCHAR (256),
@SID_varbinary VARBINARY(85),
@SID_string VARCHAR(256),
@Is_Policy BIT,
@Is_Exp BIT,
@type CHAR(1),
@Pol CHAR(3),
@Exp CHAR(3)
SET nocount ON
CREATE TABLE #logins
(
[name] NVARCHAR(128) NOT NULL,
[sid] VARBINARY(85) NOT NULL,
[type] CHAR(1) NOT NULL,
[is_policy_checked] BIT DEFAULT 0,
[is_expiration_checked] BIT DEFAULT 0,
[password_hash] VARBINARY(256)
)
INSERT #logins
(name,
sid,
type)
SELECT name,
sid,
type
FROM sys.server_principals
WHERE ( type_desc = 'SQL_LOGIN'
OR type_desc = 'WINDOWS_LOGIN' )
AND name <> 'sa'
AND name <> 'NT AUTHORITY\SYSTEM'
UPDATE a
SET a.is_policy_checked = b.is_policy_checked,
a.is_expiration_checked = b.is_expiration_checked,
a.password_hash = b.password_hash
FROM #logins a,
sys.sql_logins b
WHERE a.sid = b.sid
SET nocount OFF
IF ( @login_name IS NULL ) --Not a single user, get the list
DECLARE seelogin_curs CURSOR FOR
SELECT name,
sid,
password_hash,
type,
is_policy_checked,
is_expiration_checked
FROM #logins
WHERE name <> 'sa'
ELSE
DECLARE seelogin_curs CURSOR FOR
SELECT name,
sid,
password_hash,
type,
is_policy_checked,
is_expiration_checked
FROM #logins
WHERE name = @login_name
OPEN seelogin_curs
FETCH next FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd, @type,
@Is_Policy, @Is_Exp
IF ( @@fetch_status = -1 )
BEGIN
PRINT 'No login(s) found.'
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
END
SET @tmpstr = '/* seeMigrateSQLLogins - For SQL Server 2005 Only '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (VARCHAR, Getdate()) + ' on '
+ @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE ( @@fetch_status <> -1 )
BEGIN
IF @type = 'S'
BEGIN
PRINT '/* SQL Login ******************/'
EXEC Sp_hexadecimal
@binpwd,
@txtpwd out
EXEC Sp_hexadecimal
@SID_varbinary,
@SID_string out
IF @Is_Policy = 1
BEGIN
SET @Pol = 'ON'
END
ELSE
BEGIN
SET @Pol = 'OFF'
END
IF @Is_Exp = 1
BEGIN
SET @Exp = 'ON'
END
ELSE
BEGIN
SET @Exp = 'OFF'
END
SET @tmpstr = 'Create Login [' + Rtrim(@name)
+ '] WITH PASSWORD = ' + @txtpwd
+ ' hashed, sid = ' + @SID_string
+ ', CHECK_POLICY = ' + @Pol
+ ', CHECK_EXPIRATION = ' + @Exp
PRINT @tmpstr
PRINT ''
END
ELSE
BEGIN
PRINT '/* SQL Login ******************/'
EXEC Sp_hexadecimal
@SID_varbinary,
@SID_string out
SET @tmpstr = 'Create Login [' + Rtrim(@name)
+ '] FROM WINDOWS; '
PRINT @tmpstr
PRINT ''
END
FETCH next FROM seelogin_curs INTO @name, @SID_varbinary, @binpwd,
@type,
@Is_Policy, @Is_Exp
END
CLOSE seelogin_curs
DEALLOCATE seelogin_curs
DROP TABLE #logins
go
DECLARE @version2005 CHAR(5)
DECLARE @version2008 CHAR(5)
--Get the current version of SQL Server running
SELECT @version2005 = Substring(@@version, 29, 4)
SELECT @version2008 = Substring(@@version, 35, 4)
IF @version2005 = '9.00'
BEGIN
EXEC Seemigratesqllogins
END
ELSE IF @version2008 = '10.0'
BEGIN
EXEC Seemigratesqllogins
END
ELSE
BEGIN
EXEC Sp_help_revlogin
END
IF Object_id ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
go
IF Object_id ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
go
IF Object_id ('seeMigrateSQLLogins') IS NOT NULL
DROP PROCEDURE seemigratesqllogins
go
/* End Script */
No comments:
Post a Comment