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

This article was previously published under Q310882
This article has been archived. It is offered "as is" and will no longer be updated.
BUG #: 351595 (SHILOH_BUGS)
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'.
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     #sysloginstempFROM     sysloginsWHERE    sid = 0x00				
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 mastergodrop procedure sp_resolve_loginsgocreate 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 "' + @dest_path + '"'       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)				
Microsoft has confirmed that this is a problem in SQL Server 2000.
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.


SQL Server 2000 Books Online; topics: "How to set up and perform a log shipping role change (Transact-SQL)"; "sp_resolve_logins (T-SQL)"

Article ID: 310882 - Last Review: 01/17/2015 00:08:56 - Revision: 3.3

  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
  • kbnosurvey kbarchive kbbug kbpending KB310882