Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
KrafDinner
Starting Member
34 Posts |
 Posted - 2011-12-19 : 13:12:59
|
| I'm sure this question has been asked and answered a million times, but I'm having a difficult time getting to the bottom of it.I am migrating from one instance to another of SQL Server 2008. I am wondering if there is an easy way to copy roles from one server to the other. I realize I can script them, but that only creates the role - it doesn't do anything with the securables. The securables are somewhat complex for the roles and would take some time to recreate and then reassign each user.Is there an easy way to copy the roles and assign the users to their appropriate roles?Any help with this would be GREATLY appreciated. I've found scripts to recreate my users with their passwords, but I don't know how to recreate roles with the appropriate secured objects and users.Thanks !P.S. I'm not sure if this is in the correct forum or if I should have posted elsewhere - Sorry ! |
|
|
pnash
Starting Member
26 Posts |
Posted - 2011-12-19 : 13:59:59
|
| Here is very nice script to script out the user , roles and object level permissions http://www.sql-server-performance.com/2002/object-permission-scripts/ |
 |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-12-19 : 14:10:01
|
| Users, Roles and objects are all contained in the database. There is nothing special you need to do to 'copy' them other than backup/restore or detach/attach.What doesn't come across are the logins. To get the logins and have everything synch up correctly, google sp_help_revlogin. That is a script that will allow you to create the logins on the new server with the same SID and will not leave your users orphaned.Jeff |
|
|
|
KrafDinner
Starting Member
34 Posts |
Posted - 2011-12-19 : 14:14:59
|
| The logins I already have. I did not, however, know that the roles and security etc would come across with a backup/restore.My day has been made.@pnash - thank you for the script - that is a beautiful little piece of work that will come in handy as well.Thanks again for the responses and answers ! |
|
|
|
|
|
|
Database Role Transfer