Answered by:
Script not producing hashed password

-
This is a portion of the T-SQL stored procedure called sp_help_revlogins (see http://support.microsoft.com/kb/918992 for full script)
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
SET @PWD_varbinary = CAST( LOGINPROPERTY( 'NameOfUser', 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
PRINT @PWD_string
As long as you have the sp_hexadecimal procedure loaded, this works to produce the hashed password (as indeed the full script works too)
The colleague before me wrote a script based on sp_help_logins that also gets roles. It works EXCEPT that the hashed passwords are not showing up. They appear only as 0x. Looking at the script and comparing to sp_help_revlogin I am at a loss as to why this is not dumping the password. The portion that handles the password retreival looks identical.
Does anyone have any suggestions or comments? Thanks in advance.
/****** Object: StoredProcedure [dbo].[sp_help_revlogin_roles] Script Date: 7/7/2013 3:50:42 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin_roles]
@login_name sysname=NULL,
@databases bit=1,
@roles bit=1
WITH EXECUTE AS OWNER -- this is to allow NSCU\sa-sql-wmimonitoring to generate server login creation scripts
AS
BEGIN
SET NOCOUNT ON
DECLARE @name sysname
DECLARE @role 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 @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE @defaultlang sysname
DECLARE @crlf varchar(2)
DECLARE @return int
SET @crlf = CHAR(13) + CHAR(10)
PRINT '/* sp_help_revlogin script '
PRINT '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT ''
PRINT '/* Begin Script Logins ------------------------- */'
IF (@login_name IS NULL)
BEGIN
DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR
SELECT p.sid, p.name, p.type, p.is_disabled, ISNULL(p.default_database_name, 'master'), ISNULL(p.default_language_name, 'us_english'), 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 not in ('sa','distributor_admin','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##')
END
ELSE
BEGIN
DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR
SELECT p.sid, p.name, p.type, p.is_disabled, ISNULL(p.default_database_name, 'master'), ISNULL(p.default_language_name, 'us_english'), 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
END
OPEN rev_cursor
FETCH NEXT FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
IF (@@FETCH_STATUS = -1)
BEGIN
PRINT 'No login(s) found.'
SELECT @return = -1
GOTO Quit
END
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @name=LTRIM(RTRIM(@name))
PRINT '-- Login: ' + @name
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
PRINT 'IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = ''' + @name + ''' )'
PRINT ' 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
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
-- 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
PRINT
'IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = ''' + @name + ''' )' + @crlf +
' CREATE LOGIN ' + QUOTENAME( @name ) + @crlf +
' WITH PASSWORD = ' + @PWD_string + ' HASHED, ' + @crlf +
' SID = ' + @SID_string + ', ' + @crlf +
' DEFAULT_LANGUAGE = [' + @defaultlang + ']' +
CASE WHEN ( @is_policy_checked IS NOT NULL ) THEN ',' + @crlf + ' CHECK_POLICY = ' + @is_policy_checked END +
CASE WHEN ( @is_expiration_checked IS NOT NULL ) THEN ',' + @crlf + ' CHECK_EXPIRATION = ' + @is_expiration_checked END +
';'
END
IF (@denylogin = 1)
BEGIN -- login is denied access
PRINT 'DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
PRINT 'REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
PRINT 'ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT ' '
PRINT ' '
FETCH NEXT FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
END
PRINT '/* End Script Logins ------------------------- */'
PRINT ' '
PRINT ' '
IF @databases=1
BEGIN
FETCH FIRST FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
PRINT '/* Begin Script Default Databases ------------------------- */'
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT '-- Login: ' + @name
PRINT 'IF EXISTS ( SELECT * FROM sys.server_principals WHERE name = ''' + @name + ''' ) AND DB_ID(''' + @defaultdb + ''') IS NOT NULL'
PRINT ' ALTER LOGIN ' + QUOTENAME( @name ) + ' WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
PRINT ' '
FETCH NEXT FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
END
PRINT '/* End Script Default Databases ------------------------- */'
PRINT ' '
PRINT ' '
END
CLOSE rev_cursor
DEALLOCATE rev_cursor
IF @roles=1
BEGIN
PRINT '/* Begin Script Roles ------------------------- */'
DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR
SELECT p1.name role_principal_name, p2.name member_principal_name FROM sys.server_role_members rm
INNER JOIN sys.server_principals p1
ON p1.principal_id=rm.role_principal_id
INNER JOIN sys.server_principals p2
ON p2.principal_id=rm.member_principal_id
WHERE
p2.type IN ( 'S', 'G', 'U' )
AND p2.name not in ('sa','distributor_admin','##MS_PolicyTsqlExecutionLogin##','##MS_PolicyEventProcessingLogin##')
ORDER BY p2.principal_id
OPEN rev_cursor
FETCH NEXT FROM rev_cursor
INTO @role, @name
IF (@@FETCH_STATUS = -1)
BEGIN
PRINT '-- No role member(s) found.'
END
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'EXEC master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''' + @role + ''''
FETCH NEXT FROM rev_cursor
INTO @role, @name
END
PRINT '/* End Script Roles ------------------------- */'
PRINT ' '
PRINT ' '
CLOSE rev_cursor
DEALLOCATE rev_cursor
END
SELECT @return = 0
Quit:
RETURN @return
END
GO
Question
Answers
-
I know... not much to go on. I never did figure it out but rewrote the script as below and it works. The one above was returning NULL for this statement: SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
so perhaps there was some mix up in passing variables so that SQL did not recognize @name as a valid login. Anyway, I'm giving up fixing this one and have this one that does the same thing:
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_help_revlogin_roles_new] Script Date: 7/10/2013 9:01:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_help_revlogin_roles_new]
@login_name sysname=NULL,
@databases bit=1,
@roles bit=1
AS
BEGIN
SET NOCOUNT ON
DECLARE @name sysname
DECLARE @role 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 @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
DECLARE @defaultlang sysname
DECLARE @crlf varchar(2)
DECLARE @return int
SET @crlf = CHAR(13) + CHAR(10)
PRINT '/* sp_help_revlogin script '
PRINT '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT ''
PRINT '/* Begin Script Logins ------------------------- */'
IF (@login_name IS NULL)
BEGIN
DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR
SELECT p.sid, p.name, p.type, p.is_disabled, ISNULL(p.default_database_name, 'master'), ISNULL(p.default_language_name, 'us_english'), 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'
END
ELSE
BEGIN
DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR
SELECT p.sid, p.name, p.type, p.is_disabled, ISNULL(p.default_database_name, 'master'), ISNULL(p.default_language_name, 'us_english'), 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
END
OPEN rev_cursor
FETCH NEXT FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
IF (@@FETCH_STATUS = -1)
BEGIN
PRINT 'No login(s) found.'
SELECT @return = -1
GOTO Quit
END
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @name=LTRIM(RTRIM(@name))
PRINT '-- Login: ' + @name
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
PRINT 'IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = ''' + @name + ''' )'
PRINT ' 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
EXEC sp_hexadecimal @SID_varbinary, @SID_string OUT
-- 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
PRINT
'IF NOT EXISTS ( SELECT * FROM sys.server_principals WHERE name = ''' + @name + ''' )' + @crlf +
' CREATE LOGIN ' + QUOTENAME( @name ) + @crlf +
' WITH PASSWORD = ' + @PWD_string + ' HASHED, ' + @crlf +
' SID = ' + @SID_string + ', ' + @crlf +
' DEFAULT_LANGUAGE = [' + @defaultlang + ']' +
CASE WHEN ( @is_policy_checked IS NOT NULL ) THEN ',' + @crlf + ' CHECK_POLICY = ' + @is_policy_checked END +
CASE WHEN ( @is_expiration_checked IS NOT NULL ) THEN ',' + @crlf + ' CHECK_EXPIRATION = ' + @is_expiration_checked END +
';'
END
IF (@denylogin = 1)
BEGIN -- login is denied access
PRINT 'DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
PRINT 'REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
PRINT 'ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT ' '
PRINT ' '
FETCH NEXT FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
END
PRINT '/* End Script Logins ------------------------- */'
PRINT ' '
PRINT ' '
IF @databases=1
BEGIN
FETCH FIRST FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
PRINT '/* Begin Script Default Databases ------------------------- */'
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT '-- Login: ' + @name
PRINT 'IF EXISTS ( SELECT * FROM sys.server_principals WHERE name = ''' + @name + ''' )'
PRINT ' ALTER LOGIN ' + QUOTENAME( @name ) + ' WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
PRINT ' '
FETCH NEXT FROM rev_cursor
INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @defaultlang, @hasaccess, @denylogin
END
PRINT '/* End Script Default Databases ------------------------- */'
PRINT ' '
PRINT ' '
END
CLOSE rev_cursor
DEALLOCATE rev_cursor
IF @roles=1
BEGIN
PRINT '/* Begin Script Roles ------------------------- */'
DECLARE rev_cursor CURSOR STATIC READ_ONLY FOR
SELECT p1.name role_principal_name, p2.name member_principal_name FROM sys.server_role_members rm
INNER JOIN sys.server_principals p1
ON p1.principal_id=rm.role_principal_id
INNER JOIN sys.server_principals p2
ON p2.principal_id=rm.member_principal_id
WHERE
p2.type IN ( 'S', 'G', 'U' )
AND p2.name <> 'sa'
ORDER BY p2.principal_id
OPEN rev_cursor
FETCH NEXT FROM rev_cursor
INTO @role, @name
IF (@@FETCH_STATUS = -1)
BEGIN
PRINT '-- No role member(s) found.'
END
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT 'EXEC master.dbo.sp_addsrvrolemember @loginame=''' + @name + ''', @rolename=''' + @role + ''''
FETCH NEXT FROM rev_cursor
INTO @role, @name
END
PRINT '/* End Script Roles ------------------------- */'
PRINT ' '
PRINT ' '
CLOSE rev_cursor
DEALLOCATE rev_cursor
END
SELECT @return = 0
Quit:
RETURN @return
END
GO
- Marked as answer by Kalman TothModerator Sunday, July 21, 2013 9:06 PM