how to resolve orphan users when moving a database

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)

how to resolve orphan users when moving a database

Author

Topic

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-04-25 : 11:04:04

could anyone tell me the steps how to move a database completely from one server to another server? I did detach, and then copy the data file and the log file, and then attach them to a new server. but all users were missing.
I know I can use sp_change_users_login 'update_one' to get each user back, but I have over 20 users. how can I do it???

Please help me, Thank You!!

Feng

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 11:26:55

execute that proc 20 times ...

(or write a proc to execute that proc once for each user)

Jay White
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-25 : 12:30:36

quote:

execute that proc 20 times ...

(or write a proc to execute that proc once for each user)

OR...

Run this once in QA (make sure you are pointing to the correct user database though):

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 NOT IN ('dbo', 'guest')

OPEN curSQL

FETCH curSQL into @SQL

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

CLOSE curSQL
DEALLOCATE curSQL

go

The passwords will be blank though after you run this query. I have posted a solution on how to transfer passwords also, so do a search on isp_Transfer_Logins (use the Forum search option).

Tara

Edited by - tduggan on 04/25/2003 12:34:25

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2003-04-25 : 13:24:50

And there's always Chadmat's proc for fixing orphaned users which can be found in the SQL Team Script Library.

------------------------------------------------------
The more you know, the more you know you don't know.

fqiao70
Yak Posting Veteran

52 Posts

Posted - 2003-04-25 : 14:34:38

quote:

And there's always Chadmat's proc for fixing orphaned users which can be found in the SQL Team Script Library.

------------------------------------------------------
The more you know, the more you know you don't know.

thanks, there really helps!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources