A rewrite of the sp_help_revlogin procedure (use at own risk)

Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).

I have altered the sp_help_revlogin (from Microsoft article # 246133 )

PLEASE TEST/FIX before you use this:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_help_revlogin_2005]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS OFF

GO

CREATE PROCEDURE sp_help_revlogin_2005 @.login_name sysname = NULL AS

DECLARE @.name sysname

DECLARE @.logintype char(1)

DECLARE @.logindisabled 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, type, is_disabled FROM master.sys.server_principals

WHERE name <> 'sa' and type in ('S','U','G')

ELSE

DECLARE login_curs CURSOR FOR

SELECT sid, name, type, is_disabled FROM master.sys.server_principals

WHERE name = @.login_name

OPEN login_curs

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

IF (@.@.fetch_status = -1)

BEGIN

PRINT 'No login(s) found.'

CLOSE login_curs

DEALLOCATE login_curs

RETURN -1

END

SET @.tmpstr = '/* sp_help_revlogin_2005 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 (@.logintype = 'G' OR @.logintype = 'U')

BEGIN -- NT authenticated account/group

IF @.logindisabled = 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 IF (@.logintype = 'S')

BEGIN -- SQL Server authentication

SELECT @.binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @.SID_varbinary

IF (@.binpwd IS NOT NULL)

BEGIN -- Non-null password

EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT

SET @.tmpstr = 'SET @.pwd = CONVERT (nvarchar(128), ' + @.txtpwd + ')'

PRINT @.tmpstr

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = ''skip_encryption'''

END

ELSE BEGIN

-- Null password

EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT

SET @.tmpstr = 'EXEC master..sp_addlogin @.loginame = ''' + @.name

+ ''', @.passwd = NULL, @.sid = ' + @.SID_string

END

PRINT @.tmpstr

END

END

FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.logintype, @.logindisabled

END

CLOSE login_curs

DEALLOCATE login_curs

RETURN 0

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

I've split this from the thread to which it was posted (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169513&SiteID=1), as it does not help with decrypting passwords. I've added a more suggestive title as well.

Thanks for your contribution.

Laurentiu

|||

Here's another rewrite of the sp_help_revlogin, which uses the new DDL and also outputs the password policy options:

http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx

Thanks
Laurentiu