是否有一种方法可以同时更改SQL-server数据库中所有表的权限?

I want to change the permissions for all the tables in a SQL-Server database at once. Is there a way to do this?

我想立即更改SQL-Server数据库中所有表的权限。有办法吗?

3 个解决方案

#1

Run the results of this script (change to suit your requirements):

运行此脚本的结果(更改以符合您的要求):

SELECT
    'GRANT SELECT ON ' + OBJECT_NAME(o.object_id) + ' TO myRole'
FROM
    sys.objects o
WHERE
    OBJECTPROPERTY(o.object_id, 'IsMSSHipped') = 0
    AND
    OBJECTPROPERTY(o.object_id, 'IsTable') = 1
ORDER BY
    OBJECT_NAME(o.object_id)

#2

Provided all of your tables belong to the same schema, you could modify permissions at the Schema level.

如果您的所有表都属于同一个模式，您可以在模式级别修改权限。

See Grant Schema Permissions

看到格兰特模式的权限

#3

You can write a script that retrieves the set of tables and then grants/denies permissions through dynamic SQL.

您可以编写一个脚本，该脚本检索表集，然后通过动态SQL授予/拒绝权限。

However, I think a better approach would be to create a role, grant rights to that role, and then add/remove individuals from that role as needed.

然而，我认为更好的方法是创建一个角色，授予该角色的权利，然后根据需要从该角色中添加/删除个人。

#1