In SQL Server 2005, is there an easy way to "copy" permissions on an object from one user/role to another?

Question

I asked another question about roles and permissions, which mostly served to reveal my ignorance. One of the other outcomes was the advice that one should generally stay away from mucking with permissions for the "public" role.

OK, fine, but if I've already done so and want to re-assign the same permissions to a custom/"flexible" role, what's the best way to do that? What I've done so far is to run the Scripting wizard, and tell it to script object permissions without CREATE or DROP, then run a find-replace so that I wind up with a lot of "GRANT DELETE on [dbo.tablename] TO [newRole]". It gets the job done, but I feel like it could be prettier/easier. Any "best practice" suggestions?

Answer 1

The idea of having a role is that you only need to setup the permissions once. You can then assign users, or groups of users to that role.

It's also possible to nest roles, so that a role can contain other roles.

Not sure if its best practice, but it makes sense that if you have a complex set of permissions, with groups of users that need access to multiple applications you go something like:

NT User -> NT Security Group -> SQL Server Role -> SQL Server Role A, Role B ...

Answer 2

Working from memory (no SQL on my gaming 'pooter), you can use sys.database_permissions

Run this and paste the results into a new query. You may need to pimp it to support schemas (dbo.) by joining onto sys.objects

SET NOCOUNT ON;
DECLARE @NewRole varchar(100), @SourceRole varchar(100);

-- Change as needed
SELECT @SourceRole = 'Giver', @NewRole = 'Taker';

SELECT
    state_desc + ' ' + permission_name + ' ON ' + OBJECT_NAME(major_id) ' TO ' + @NewRole
FROM
    sys.database_permissions
WHERE
    grantee_principal_id = DATABASE_PRINCIPAL_ID(@SourceRole) AND
    -- 0 = DB,  1 = object/column, 3 = schema. 1 is normally enough
    class <= 3