Extraer Inicios de Sesión, SID y Contraseña en SQL Server 2005

Intentaré completarlo para extraer también las propiedades de política de cuentas…….

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)

DECLARE

 @pwd varchar (256)

IF

 (@login_name IS NULL)

DECLARE

 login_curs CURSOR FOR

SELECT sid, name, is_disabled, password_hash FROM master.sys.sql_logins

WHERE

 type='S' AND name <> 'sa'

ELSE

DECLARE

 login_curs CURSOR FOR

SELECT sid, name, is_disabled, password_hash FROM master.sys.sql_logins

WHERE

 type='S' 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='CREATE LOGIN [' + @name +'] FROM WINDOWS'

PRINT

 @tmpstr

SET

 @tmpstr='DENY CONNECT SQL TO[' + @name +']'

PRINT

 @tmpstr

END

ELSE

 BEGIN -- NT login has access

SET

 @tmpstr='CREATE LOGIN [' + @name +'] FROM WINDOWS'

PRINT

 @tmpstr

END

END

ELSE

 BEGIN -- SQL Server authentication

IF

 (@binpwd IS NOT NULL)

BEGIN

 -- Non-null password

EXEC

 sp_hexadecimal @binpwd, @txtpwd OUT

EXEC

 sp_hexadecimal @SID_varbinary,@SID_string OUT

SET

 @tmpstr = 'CREATE LOGIN ' +@name+ ' WITH PASSWORD='+ @txtpwd+ ' HASHED,SID=' + @SID_string

END

ELSE

 BEGIN

-- Null password

EXEC

 sp_hexadecimal @SID_varbinary,@SID_string OUT

SET

 @tmpstr = 'CREATE LOGIN ' +@name+ ' WITH PASSWORD='''',SID=' + @SID_string

END

PRINT

 @tmpstr

END

END

FETCH

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

END

CLOSE

 login_curs

DEALLOCATE

 login_curs

RETURN

 0