June 25, 2008 at 7:14 am
I would like to know that if you restore a database on a different server (in case of hardware failure for example) how to restore the specific users for that database (with the security role's).
because the master database isn't a part of the restore the user does not exists.
because we have many database users configured, it would take a lot of time to create each user by hand.
thx
wietse
June 25, 2008 at 8:11 am
well that is the reason the DBA's are on job.
Do you have the backup of the master database, from the server.
You should have regularly scripted out the logins for backup.
Well I dont recall any other way to get those logins back, Incase of NT Logins, you dont have to worry much but Just apply the users and groups and you will be good to go. But for the sql logins, not hope.
Here is a Quick Script you can run to get a list of users in each Database.
exec sp_msforeachdb @command1='USE [?] Select ''?'' as dbname, name from sysusers'
Atleast you can get a list of users,pull them down in an excel sheet and do the sorting etc.
Maninder
www.dbanation.com
June 26, 2008 at 8:43 am
If the Master database of the original instance is accessible, you can use the method described in http://support.microsoft.com/kb/246133/en-us. If it's not, I think you're out of luck. That's why Master should be backed up regularly.
Greg
June 26, 2008 at 8:56 am
I Agree with all that has been said before.
If you don't have access to the existing master db, a backup of it or don't have the logins scripted out then you are out of luck.
You can use sp_change_users_logins to create logins on the new server based on database users if the login does not exists, which may save you some time but things like passwords etc will be gone.
Look up SP_Change_Users_login in BOL
Gethyn Elliswww.gethynellis.com
June 27, 2008 at 6:34 am
One of the ways I manage my logins is I use group policies for some if possible, and keep records of the logins for all the databases. It easier to readd the group then the individuals. The rest I got into the habit of having a permissions script, mostly for stored proc access. You can then run the script on the databases and that will add them back, also lets one know what logins are needed for each database and will need to be readded. Managing 60 databases on one server is my motivation to keep the scripts updated.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply