Microsoft KB Archive/310882

= BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log Shipping Role Change =

Article ID: 310882

Article Last Modified on 2/22/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Enterprise Edition
 * Microsoft SQL Server 2000 Developer Edition

-



This article was previously published under Q310882



BUG #: 351595 (SHILOH_BUGS)



SYMPTOMS
During a Log Shipping role change an attempt to execute the sp_resolve_logins stored procedure fails. The following error message occurs when you execute the sp_resolve_logins stored procedure from a recently recovered secondary database:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'syslogins'.



CAUSE
The Transact-SQL code for the sp_resolve_logins stored procedure incorrectly uses the syslogins system table (syslogins resides in the master database). The following code in the stored procedure causes the error to occur: SELECT  * INTO    #sysloginstemp FROM    syslogins WHERE   sid = 0x00



WORKAROUND
To work around this problem, perform a complete backup of the master database. Use the following script to re-create the sp_resolve_logins stored procedure in the master database: use master go drop procedure sp_resolve_logins go create procedure sp_resolve_logins @dest_db        sysname ,@dest_path      nvarchar(255) ,@filename       nvarchar(255) as -- Setup run-time options and -- Declare variables. SET NOCOUNT ON  DECLARE   @retcode         int            -- Return value of xp call. ,@datafiletype   varchar(255) ,@command        nvarchar(255) ,@lgnname        sysname ,@lgnsid         varbinary(85) ,@usrname        sysname

-- Check permissions. IF (ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0) <> 1) BEGIN RAISERROR(15247, 16, 1) RETURN(1) -- Failure END

-- Error if in user transaction. IF @@trancount > 0 BEGIN raiserror(15289,-1,-1) RETURN (1) END

-- Validate the directory the dat file is in. -- Remove heading and trailing spaces. SELECT @dest_path = RTRIM(LTRIM(@dest_path)) -- If the last char is '\', remove it. IF substring(@dest_path, len(@dest_path),1) = '\' SELECT @dest_path = substring(@dest_path, 1, len(@dest_path)-1)

-- Do not do validation if it is a UNC path due to security problem. -- If the server is started as a service using local system account, we  -- do not have access to the UNC path. IF substring(@dest_path, 1,2) <> '\\' BEGIN SELECT @command = 'dir &quot;' + @dest_path + '&quot;' exec @retcode = master..xp_cmdshell @command, 'no_output' IF @@error <> 0 RETURN (1) IF @retcode <> 0 BEGIN raiserror (14430, 16, -1, @dest_path) RETURN (1) END END

-- 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    master.dbo.syslogins WHERE   sid = 0x00

truncate TABLE #sysloginstemp

-- BULK INSERT the file into the temp table. SET     @dest_path = @dest_path + '\' + @filename SET     @datafiletype   =  widenative

EXEC('       BULK INSERT #sysloginstemp         FROM  + @dest_path +         WITH ( DATAFILETYPE = ' + @datafiletype + ' ,KEEPNULLS)      ')

-- UPDATE the SID in the destination database to the value in the current server's   -- syslogins table ensuring that the names match between the source and destination -- syslogins tables. Do this by cursoring through each login and executing -- sp_change_users_login for each login that require a SID resynch.

-- DECLARE & OPEN CURSOR over old login names DECLARE loginmapping CURSOR LOCAL FOR SELECT name, sid FROM #sysloginstemp OPEN loginmapping

FETCH loginmapping INTO @lgnname, @lgnsid WHILE (@@fetch_status >= 0) BEGIN

-- GET NAME OF USER THAT NEEDS TO BE RE-MAPPED FOR THIS LOGIN SELECT @usrname = NULL     -- INIT TO NULL IN CASE OF NO MATCH SELECT @usrname = u.name FROM dbo.sysusers u           ,master.dbo.syslogins l         WHERE u.sid = @lgnsid AND l.loginname = @lgnname AND l.sid <> u.sid -- If we have a user name, do the remapping. IF @usrname IS NOT NULL EXEC ('EXEC ' + @dest_db + '.dbo.sp_change_users_login Update_One, ' + @usrname + ',' + @lgnname)

-- Get next login-mapping. FETCH loginmapping INTO @lgnname, @lgnsid END

CLOSE loginmapping DEALLOCATE loginmapping

-- Return Success/Failure IF @@ERROR <> 0 RETURN (1) RETURN (0)



STATUS
Microsoft has confirmed that this is a problem in SQL Server 2000.



MORE INFORMATION
The syslogins table holds information about the logins that exist on the server. The sp_resolve_logins stored procedure uses information from the syslogins table along with a BCP file of the syslogins table from the previous Primary server and sysusers table from the recently recovered secondary database, to map the logins.