Capture Logins para SQL2008 R2

This question is answered

Que tal Comunidad, buenas noches

Tengo un problema con el script 'KB878449_Capture_Logins.sql' al momento de ejecutarlo en un servidor con SQL2008 R2. El error es el siguiente:

Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
El nombre de objeto 'master..sysxlogins' no es válido.

Alguien de Uds tendra un script que se ejecute correctamente?

 

 

Gracias por la atencion, saludos

 

Verified Answer
  • Hi!

    my email:  frank_zg@hotmail.com

    thanks for your help

  • This is the correct Script. Good luck

    /************************************************************************************************

    *

    * Purpose: To capture all the SQL Server logins with the binary password regardless of SQL version.  

    * Script is compatiable 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: Inital - 01/13/2006

    *

    * Revsions: 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 */

All Replies
  • hi,

    i can help you to resolve your probleme, just send me email and i send to you the script modified for your need SQL2008R2

  • Hi!

    my email:  frank_zg@hotmail.com

    thanks for your help

  • This is the correct Script. Good luck

    /************************************************************************************************

    *

    * Purpose: To capture all the SQL Server logins with the binary password regardless of SQL version.  

    * Script is compatiable 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: Inital - 01/13/2006

    *

    * Revsions: 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 */

  • Hi

    Aissam

    can you help me sending a email with script modified for SQL 2008 R2

    Me email is jquinones@iconbusinessconsulting.com

    thank you