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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy