Orpahned users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Orpahned users

Author

Topic

rkc01
Starting Member

43 Posts

Posted - 2003-02-27 : 15:28:23

http://support.microsoft.com/default.aspx?scid=KB;en-us;q274188

This link points to a KB article that supposed to help id and fix orphaned users. I tried it and got back this error:

Server: Msg 15291, Level 16, State 1, Procedure sp_change_users_login, Line 95
Terminating this procedure. The Login name '%d' is absent or invalid.

I restored a database from a customers SQL 7.0 Server to my SQL 7.0 Server. Obviously I have users in that DB that don't exist in sysxlogins in my master DB. I don't see how this process could even work to begin with. The article says:

'...This relinks the server login "test" with the the Northwind database user "test".'

How can the database user be relinked to a login that never existed in the first place? Bottom line is that I'm looking for ways to resolve the orphaned users. I have a script that will transfer the logins to the new server but I have to have a connection to the old server. Looking for a way to recreate the logins and pw without having access to the old box. I've been through at least 12 KB articles, all of them dead ends.

thx,

-Rob

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-02-27 : 15:44:34

You will not be able to transfer the passwords unless you can access the old server. You should transfer the accounts and passwords first, then run this sp_change_users_login script (run it in the user database):
SET QUOTED_IDENTIFIER OFF
GO

DECLARE @SQL varchar(100)

DECLARE curSQL CURSOR FOR
select "exec sp_change_users_login 'AUTO_FIX','" + name + "'"
from sysusers
where issqluser = 1 and name <> 'dbo'

OPEN curSQL

FETCH curSQL into @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL)
FETCH curSQL into @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

go

I believe this script gets an error for the guest account, so either ignore the one error or modify the script to ignore the guest account.

rkc01
Starting Member

43 Posts

Posted - 2003-02-27 : 15:49:35

Thanks Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources