problem with sp_resolve_logins

I've set up SQL Server log shipping and I'm testing the failover process.
BOL says to run sp_resolve_logins from the target database. That failed for
me with the error: "Invalid object name 'syslogins'."
I searched google, and the consensus was that sp_resolve_logins has to be
run from master. When I did this, the procedure executed successfully, but
all the users in my database remained orphaned.
Next, I ran sp_helptext on sp_resolve_logins to see what's really happening.
It looks like when I ran it the first time from the target database it
failed on this (line 59):
SELECT *
INTO #sysloginstemp
FROM syslogins
WHERE sid = 0x00
because syslogins isn't qualified with master.dbo.
When run from master, it looks the reason it didn't fix the logins is
because of this line (93):
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
Since I had to run it from master, it is going to check the sysusers table
of master, not my database, so there won't be any users to resolve.
Can someone tell me if I missed something important here? I came up with a
workaround, but I'd prefer to do it by the book.
I'm using SQL Server 2000 SP3. This particular instance was on the
developer edition, but I looked at sp_resolve_logins on an enterprise
edition machine and it's the same.
Thanks in advance for any insight,
Mike FormanHi Michael,
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
You may check the following article.
310882 BUG: sp_resolve_logins Stored Procedure Fails If Executed During Log
http://support.microsoft.com/?id=310882
Bill Cheng
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--
| From: "Michael Forman" <mforman@.factset.com>
| Subject: problem with sp_resolve_logins
| Date: Wed, 6 Aug 2003 16:49:03 -0400
| Lines: 41
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OpdStwFXDHA.536@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: inetuser.factset.com 164.55.1.103
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:299890
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| I've set up SQL Server log shipping and I'm testing the failover process.
| BOL says to run sp_resolve_logins from the target database. That failed
for
| me with the error: "Invalid object name 'syslogins'."
|
| I searched google, and the consensus was that sp_resolve_logins has to be
| run from master. When I did this, the procedure executed successfully,
but
| all the users in my database remained orphaned.
|
| Next, I ran sp_helptext on sp_resolve_logins to see what's really
happening.
| It looks like when I ran it the first time from the target database it
| failed on this (line 59):
| SELECT *
| INTO #sysloginstemp
| FROM syslogins
| WHERE sid = 0x00
|
| because syslogins isn't qualified with master.dbo.
|
| When run from master, it looks the reason it didn't fix the logins is
| because of this line (93):
| 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
|
| Since I had to run it from master, it is going to check the sysusers table
| of master, not my database, so there won't be any users to resolve.
|
| Can someone tell me if I missed something important here? I came up with
a
| workaround, but I'd prefer to do it by the book.
|
| I'm using SQL Server 2000 SP3. This particular instance was on the
| developer edition, but I looked at sp_resolve_logins on an enterprise
| edition machine and it's the same.
|
| Thanks in advance for any insight,
| Mike Forman
|
|
|