Change users login after a SqlServer 2000 Restore or a Detach Attach
Feb 22nd, 2007 by Onyrix 728 Views | 
Email This Post
| 
Print This Post
(3 votes, average: 5 out of 5)
Loading ...
Problem:
You make a SqlServer 2000 DB backup from server A.
You need to restore it on server B.
In alternative suppose you detach a DB from server A to attach it on server B (copying the MDF, NDF and LDF files).
After the restoration, what you get at 99% is that to the DB users do not correspond any security LOGIN.
So you have to rebuild this lost link…
Solution:
First of all get a report of the unlinked users logins:
If there is an old login that gives you problems, delete it.
sp_change_users_login ‘report’
Then (re)create the login you need.
Then link the new login with users using the following stored procedure:
Here the syntax:
EXEC sp_change_users_login ‘Update_One’, ‘username’,
‘loginname’
del.icio.us
Digg
Furl
Reddit
Technorati
Stumbleupon