June 4, 2009 at 6:19 am
In my work, I do a lot of database restorations. I've found that every time I restore a database, I am forced to recreate the user that we log in with. The user still exists in the database, but it's like his permissions don't get restored along with him. Is there something to the backup process that we're missing?
June 4, 2009 at 6:35 am
That should only happen with SQL authenticated ids and restoring onto a different SQL instance. I presume this is your scenario.
use sp_change_users_login 'update_one','username','login' to tie the user up to its login again.
Its because the sid does not match on the two servers. If you use sp_help_revlogin to copy the login from the source to the target server this won't happen (as it maintains the sid value)
---------------------------------------------------------------------
June 4, 2009 at 6:38 am
Hi Mark,
A couple of quick questions; Are you restoring these backups to the same server that they were taken on? Also, is the user in question an SQL or windows authentication user?
Try looking up orphaned users in BOL and see if that sounds like the issue you are having.
Cheers
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
June 4, 2009 at 6:47 am
Thanks for the replies George and Rob.
As George guessed, the users are sql authenticated and the restores are not on the same server. Do I have to exectute sp_help_revlogin before backing up the db? Is there a way to tie this in to the backup process automatically?
June 4, 2009 at 7:02 am
Hi Mark,
What George is saying is that the concept of Users is in the database, and Logins is at the server level. When you backup the database, you are also backing up the users in that database, but at restore the logins remain on the server you restore to. These aren't linked to the users by name, but rather by an sid which will need to match for the person to be able to login and use the database.
When you restore the database, you can run sp_change_users_login specifying the user and login to change the sid stored in the database to match the login, or you can use sp_help_revlogin to script out the logins on the source server and apply them to the destination server (you will need to do this only once, not part of the backup process), eliminating the need to relink the login every time. For creating the sp and more info see here: http://support.microsoft.com/kb/246133
Hope that helps!
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
June 4, 2009 at 7:30 am
Got it. That's exactly what I needed! Thanks for the help!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply