Question Status

Verified
FrankIcon asked a question on 17 Aug 2011 6:53 PM

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

 

Reply
Aissam Ait Kacem responded on 18 Aug 2011 9:28 AM

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

Reply
Verified Answer
FrankIcon responded on 18 Aug 2011 6:36 PM

Hi!

my email:  frank_zg@hotmail.com

thanks for your help

Reply
Verified Answer
FrankIcon responded on 26 Aug 2011 9:39 AM

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

Reply
JoseqIc0n responded on 30 Mar 2013 11:12 AM

Hi

Aissam

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

Me email is jquinones@iconbusinessconsulting.com

thank you

Reply
Verified Answer
FrankIcon responded on 18 Aug 2011 6:36 PM

Hi!

my email:  frank_zg@hotmail.com

thanks for your help

Reply
Verified Answer
FrankIcon responded on 26 Aug 2011 9:39 AM

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

Reply