Knowledge Base

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

Keywords: kbbug kbpending KB310882

Microsoft KB Archive/310882

Contents