Scripting MS SQL Server 2000/2008 Roles

Question

I'm currently migrating a MS SQL Server from 2000 to 2008. I really want to migrate all of the roles (including all members and permissions of that role) from the 2000 box by scripting them.

Then, on the 2008 box i want to edit them and again script them to move them to the live server.

It seems that scripting the role simply allows you to recreate the role and does not include any details of the members and permissions. Can this be done? Is it as easy as selecting script role in Management Studio or must I write the script myself, if so, do you have any pointers (which tables to use etc)?

Answer 1

You will not need to script out roles/ permissions if you're upgrading the databases (i.e. either by a backup/restore or a detach/attach). You will only need to script logins out (which are at the database server level, not the database level). For that, there's the tool sp_help_revlogin.

If you are re-creating the db in 2008 from scratch then there are some ready made scripts that come with FineBuild that will let you script roles & permissions out from a db. Then you can modify these in t-sql as you see fit before apply to the new 2008 db.

Let me know if you need clarification.