August 14, 2009 at 8:35 pm
Hi All,
I have taken a backup from production and restored on a brand new testing machine. How do i fix the users. What are the steps involved in it!
Also, what is difference between
sp_change_users_login Auto_FIX switch and
sp_change_users_login update_one switch.
In which scenarios, shall i use above two????
Seeking for more inputs!!!
August 15, 2009 at 4:58 am
All you need to do is go to microsoft site and find the SP sp_help_revlogin.
A Diff SP is available for both versions SQL 2000 & SQL 2005.
Then after creating this SP in your master DB of your OLD Server you'll get a list of logins with same SID, copy the result and run it on NEW Server.
Most of the time this will solve the problem.
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 15, 2009 at 5:00 am
This is the link
http://support.microsoft.com/kb/246133
and if you want to use sp_change_users_login
Auto_Fix
Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name. If a login with the same name does not exist, one will be created. Examine the result from the Auto_Fix statement to confirm that the correct link is in fact made. Avoid using Auto_Fix in security-sensitive situations.
When you use Auto_Fix, you must specify user and password if the login does not already exist, otherwise you must specify user but password will be ignored. login must be NULL. user must be a valid user in the current database. The login cannot have another user mapped to it.
Report
Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login. user, login, and password must be NULL or not specified.
Update_One
Links the specified user in the current database to an existing SQL Server login. user and login must be specified. password must be NULL or not specified.
eNJOY 😎
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
August 15, 2009 at 5:43 am
Thanks Dude! 🙂
August 15, 2009 at 9:29 am
That works if the logins are new. If there is an existing login, you would need to use sp_change_users_login. As to auto or one, it depends. Auto should fix all broken ones. One fixes a specific one.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply