restore members of server roles | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

restore members of server roles

I’m hoping some of you can help me out.<br /><br />When I restore one DB, I used the procedure sp_help_revlogin to create logins with the original SID and password on the destination server and it works fine, some of these logins are members of system administrators role in the source server. After I checked the logins member of System Administrator role on destination server, I DID NOT find those members as they were members of system administrator role on the source server.<br /><br />So how can I create logins users along with their membership on these roles?<br /><br />Thanks in advance [<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]<br />Essam<br /><br />
All the permission changes should be scripted from source server and apply them on to the target server using scripts (easy way). Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thanks Satya.. I tried the scripts, but it gave permissions to DB roles NOT the server roles ( like if there are login users and members of System Administrator role ) they will not appear in the target server. Any idea ? Regards. Essam
http://www.sql-server-performance.com/bm_object_permission_scripts.asp you can review this link and replace SP_ADDSRVROLEMEMBER to script. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Thank u Satya..
it works fine now.. Essam
Hi All,
I have exact same problem. After restoring All DBs to another server, I use sp_help_revlogin to script out logins and passwords and run the output on the destination server. It does a great job of moving logins/passwords however, it does not copy Server roles (i.e. user with system administrators role in source db doesn’t get this granted on destination server)
Can someone please explain what Satya means:http://www.sql-server-performance.com/bm_object_permission_scripts.asp you can review this link and replace SP_ADDSRVROLEMEMBER to script. what exactly do I replace sp_addsrvrolemember with? Also, If I use the scripts in the above article I don’t need to use sp_help_revlogin, correct? Many Thank
]]>