在SQL Server 2005中，是否有一种简单的方法可以将对象的权限从一个用户/角色“复制”到另一个用户/角色？

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?

好的,很好,但是如果我已经这样做并且想要为自定义/“灵活”角色重新分配相同的权限,那么最好的方法是什么?到目前为止我所做的是运行Scripting向导,并告诉它在没有CREATE或DROP的情况下编写脚本对象权限,然后运行一个find-replace,这样我就可以在[dbo.tablename]上找到很多“GRANT DELETE” TO [newRole]“。它完成了工作,但我觉得它可以更漂亮/更容易。任何“最佳实践”建议?

2 个解决方案

#1

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

在内存中工作(我的游戏'pooter上没有SQL),您可以使用sys.database_permissions

Run this and paste the results into a new query.

运行此选项并将结果粘贴到新查询中。

Edit, Jan 2012. Added OBJECT_SCHEMA_NAME.
~~You may need to pimp it to support schemas (dbo.) by joining onto sys.objects~~

编辑,2012年1月。添加了OBJECT_SCHEMA_NAME。您可能需要通过加入sys.objects来将其设置为支持模式(dbo。)

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_SCHEMA_NAME(major_id) + '.' + 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;

#2

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 ...

NT用户 - > NT安全组 - > SQL Server角色 - > SQL Server角色A,角色B ...

#1