Copying roles and permissions between databases

[cliffcustomade]

Is there any way I can copy table permissions for user roles between databases? More specifically, can I copy/overwrite permissions for the 'public' role?

I am able to set up a DTS, or use the Export wizard to succesfully copy users, and roles between SQL databases using Enterprise Manager. Thus, users are set up sucessfully, and when I look at the 'public' role, I find them correctly assigned. However, when I look at the table permissions for roles in the newly copied version, they do not match those from the original.

I am trying to avoid having to manually reset the permissions in my copied roles. Is this possible?

 

[ClaireHCM]

Did you use DTS package (Copy SQL Server Objects Task). In the Copy tab, uncheck the "Use Default options" , check "Copy SQL Server logins(Windows and SQL Server logins)", this will prevent orphant user, role or permission issues.

 

[cliffcustomade]

Thanks for the suggestion, however in my DTS (Copy SQL Server objects task) I had already done this.

In the copy tab I have checked 'Create Destination objects' and all 3 sub-selections. I have checked the Copy Data and Replace existing data selections, and unchecked the 'use collation', 'copy all objects' and Use default options'.

Any other suggestions? As I say, I can copy the users no problem; the role is copied and the users are assigned correctly; it is just the permissions which are not copied.

Thanks