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

Article translations Article translations
Article ID: 310882 - View products that this article applies to.
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)
Expand all | Collapse all

On This Page

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 "' + @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)
				

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.

REFERENCES

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

Properties

Article ID: 310882 - Last Review: January 17, 2015 - Revision: 3.3
APPLIES TO
  • Microsoft SQL Server 2000 Enterprise Edition
  • Microsoft SQL Server 2000 Developer Edition
Keywords: 
kbnosurvey kbarchive kbbug kbpending KB310882

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com