How do I copy Server Roles from master db?

Last Post 13 Jul 2004 12:58 PM by brianvan. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
brianvan
New Member
New Member

--
08 Jul 2004 02:01 PM
Hi,
During our last Disaster Recovery (DR) test, we were successful at recovering all of our SQL Server instances at a remote site, on different hardware. With one exception. The Server Roles for our logins did not come back. All the logins worked that had specific Database Access, but our super user account that has no Database Access (only a Server Role of System Administrator) had no privileges. Here is what we do to prepare for DR: Each night we run a stored procedure from Microsoft called sp_help_revlogin. It copies the logins from the master database and stores them in a table (we modified it to do the table save, the default is to output to the screen). At DR we do scratch installs of SQL Server, using the same drive letters, we restore all databases except master, and then select the logins out of the table they were saved to and put them back into the master database. This worked great with the exception of those logins that had Server Roles. The login came back but the Server Role did not. How can I extract the Server Roles from the master database? Thank you in advance for any help.
brianvan
New Member
New Member

--
09 Jul 2004 05:30 AM
Sorry, I should have been more specific. I would like to script them, but I do not know what views to extract them from. I do not know where they are stored. Thanks.
brianvan
New Member
New Member

--
13 Jul 2004 12:58 PM
I found InstantDoc ID 41181 which was extremely well done and usefull. Thank you.
You are not authorized to post a reply.

Acceptable Use Policy