Friday, March 9, 2012

Help - sp_help_revlogin does not work in 2005

I compiled the sp_help_revlogin code on my 2005 server.
However, when I try to execute it I get:
Msg 208, Level 16, State 1, Procedure sp_help_revlogin, Line 12
Invalid object name 'master..sysxlogins'.
This has always worked fine in 2000, but apparently one of the
underlying tables has changed or been renamed.
Has anyone run into this problem and how do you fix it?
If you can't use sp_help_revlogin, how do you migrate your logins
otherwise? I've never done it any other way.
Thanks
I have used this update successfully:
http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the link. However, when I run the output, for each CREATE
LOGIN stmt, I get this error:
Msg 15433, Level 16, State 1, Line 6
Supplied parameter sid is in use.
However, these logins do not yet exist on the server.
?
Paul Ibison wrote:
> I have used this update successfully:
> http://blogs.msdn.com/lcris/archive/2006/04/03/567680.aspx
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Sorry to ask the obvious, but are you sure you're using this output on the
destination server
It's just that I find it very strange that these SIDs have been used.
Have a look at the sys.server_principals table to see the names of the
logins that are using your SIDs.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||No problem. As per your suggestion, I look in the principals table, and
lo and behold, all the logins were already there. How they got there
before I ran the sp_rev_login script, I'm not sure. Nor were they
showing up under Logins in the Management Studio console, even when I
refreshed the list. However, by reconnecting, they showed up when I
refresh.
But I'm still not sure how the logins could have already been there
before I ran the sp_help_revlogin script, as this is an entirely new
installation of SQL Server on a brand new server. Any ideas?
Paul Ibison wrote:
> Sorry to ask the obvious, but are you sure you're using this output on the
> destination server
> It's just that I find it very strange that these SIDs have been used.
> Have a look at the sys.server_principals table to see the names of the
> logins that are using your SIDs.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||The create date in the sys.server_principals view might give a bit of a
clue.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

No comments:

Post a Comment