May 7, 2008 at 10:12 am
This is probably a really rookie question, but how do I restore a logon's User Mapping and permissions to a database when restoring that database? I have a production DB that is backed up nightly and restored immediately to another server to serve as a reporting DB. When the restore is done, the logons lose their memory of having access to that DB and the only way I know to restore them is to go in and manually do it every morning.
There has *got* to be a better way. I hope someone can point me to it.
May 7, 2008 at 11:50 am
You can perform the following,
1. After restoring if there is any mismatch ids (there might be some) you need to make use of sp_change_users_login procedure to map the login to a user. Refer, http://msdn.microsoft.com/en-us/library/aa259633(SQL.80).aspx
2. If you do not want to make use of step1, before restoring you need to script all the logins in source server using sp_help_revlogin and run the script in destination server and then restore the database so that mismatch ids will not be present.
[font="Verdana"]- Deepak[/font]
May 7, 2008 at 12:00 pm
Deepak, thanks for your reply. I have downloaded the relevant msdn articles and will try them.
M.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply