Bug in sp_resolve_logins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Bug in sp_resolve_logins

Author

Topic

srad
Starting Member

39 Posts

Posted - 2004-04-14 : 07:04:52

I have been working on log shipping for and just realised a bug in the sp sp_resolve_logins if you try to update user sid's in any other db than master.

The sp remaps sid's in syslogins and sysusers but on line 59 the code looks like this...
-- CREATE the temp table for the datafile
-- This method ensures we are always getting the
-- real table definition of the syslogins table.
SELECT *
INTO #sysloginstemp
FROM syslogins
WHERE sid = 0x00

If the value of @dest_db is <> master then the sp returns...
Invalid object name 'syslogins'.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-14 : 09:54:13

Here...use these two instead:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE sp_help_revlogin @login_name
sysname = NULL
AS
--Declare needed variables.
DECLARE
@name sysname,
@xstatus int,
@binpwd varbinary (256),
@txtpwd sysname,
@tmpstr varchar (256),
@SID_varbinary varbinary(85),
@SID_string varchar(256),
@dbname varchar(255)
--Determine whether to process one login or all. Set up cursor accordingly.
IF (@login_name IS NULL)
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> 'sa'
END
ELSE
BEGIN
DECLARE login_curs CURSOR FOR
SELECT
sxl.sid,
sxl.name,
sxl.xstatus,
sxl.password,
sd.name AS dbname
FROM
master..sysxlogins sxl
INNER JOIN master..sysdatabases sd ON sxl.dbid = sd.dbid
WHERE
sxl.srvid IS NULL
AND sxl.name <> @login_name
END
OPEN login_curs
FETCH NEXT FROM login_curs
INTO
@SID_varbinary,
@name,
@xstatus,
@binpwd,
@dbname
--If no logins found, exit the procedure.
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SELECT @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SELECT @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
BEGIN
SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
END
ELSE
BEGIN
SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
END

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
BEGIN

-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr =
'EXEC master..sp_addlogin ''' + @name + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
END
END
IF (@xstatus & 2048) = 2048
BEGIN
-- login upgraded from 6.5
SET @tmpstr = @tmpstr + '''skip_encryption_old'''
PRINT @tmpstr
END
ELSE
BEGIN
SET @tmpstr = @tmpstr + '''skip_encryption'''
PRINT @tmpstr
END
END
--Add the default database.
SET @tmpstr = 'EXEC master..sp_defaultdb ''' + @name + ''',''' + @dbname + ''''
PRINT @tmpstr
END
FETCH NEXT FROM login_curs
INTO
@SID_varbinary,
@name,
@xstatus,
@binpwd,
@dbname
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0

GO

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources