none
Script not producing hashed password

    Question

  • 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

    Tuesday, July 09, 2013 10:20 PM

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


    Wednesday, July 10, 2013 4:13 PM