实现将授予数据库角色对数据库中所有用户表的权限的SQL脚本的最佳方式是什么?

What's the best way to implement a SQL script that will grant select, references, insert, update, and delete permissions to a database role on all the user tables in a database?

实现一个SQL脚本的最佳方法是，在数据库中对所有用户表的数据库角色授予选择、引用、插入、更新和删除权限。

Ideally, this script could be run multiple times, as new tables were added to the database. SQL Server Management Studio generates scripts for individual database objects, but I'm looking for more of a "fire-and-forget" script.

理想情况下，这个脚本可以运行多次，因为新表被添加到数据库中。SQL Server Management Studio为单个数据库对象生成脚本，但我正在寻找更多的“fire-and-forget”脚本。

5 个解决方案

#1

Dr Zimmerman is on the right track here. I'd be looking to write a stored procedure that has a cursor looping through user objects using execute immediate to affect the grant. Something like this:

齐默尔曼医生说得对。我希望编写一个存储过程，使用execute immediate在用户对象中使用游标循环来影响授予。是这样的:

 IF EXISTS (
    SELECT 1 FROM sysobjects
    WHERE name = 'sp_grantastic'
    AND type = 'P'
)
DROP PROCEDURE sp_grantastic
GO
CREATE PROCEDURE sp_grantastic
AS
DECLARE
 @object_name VARCHAR(30)
,@time       VARCHAR(8)
,@rights     VARCHAR(20)
,@role       VARCHAR(20)

DECLARE c_objects CURSOR FOR
    SELECT  name
    FROM    sysobjects
    WHERE   type IN ('P', 'U', 'V')
    FOR READ ONLY

BEGIN

    SELECT  @rights = 'ALL'
           ,@role = 'PUBLIC'

    OPEN c_objects
    WHILE (1=1)
    BEGIN
        FETCH c_objects INTO @object_name
        IF @@SQLSTATUS <> 0 BREAK

        SELECT @time = CONVERT(VARCHAR, GetDate(), 108)
        PRINT '[%1!] hitting up object %2!', @time, @object_name
        EXECUTE('GRANT '+ @rights +' ON '+ @object_name+' TO '+@role)

    END

    PRINT '[%1!] fin!', @time

    CLOSE c_objects
    DEALLOCATE CURSOR c_objects
END
GO
GRANT ALL ON sp_grantastic TO PUBLIC
GO

Then you can fire and forget:

然后你就可以忘记:

EXEC sp_grantastic

#2

I'm sure there is an easier way, but you could loop through the sysobjects table in the database and grant permissions to any user table objects that exist. You could then run that multiple times whenever new tables are added.

我确信有一种更简单的方法，但是您可以遍历数据库中的sysobjects表，并授予存在的任何用户表对象权限。然后，每当添加新表时，您可以多次运行这个表。

#3

There's an undocumented MS procedure called sp_MSforeachtable that you could use which is definitely in 2000 and 2005.

有一个没有文档说明的MS过程叫做sp_MSforeachtable，你可以在2000年和2005年使用它。

To grant select permissions the usage would be:

要授予select权限，使用方法如下:

EXECUTE sp_MSforeachtable @command1=' Grant Select on ? to RoleName'

To grant the other permissions either have a new statement for each one or just add them to the command like this:

要授予其他权限，要么为每个权限都设置一个新的语句，要么将它们添加到如下命令中:

EXECUTE sp_MSforeachtable @command1=' Grant Select on ? to RoleName; Grant Delete on ? to RoleName;'

With a bit of playing around it might be possible to turn the role name into a parameter as well.

稍微改变一下，也可以将角色名转换为参数。

#4

We use something similar where I work. Looping through every Tables, Views, Stored Procedures of the system.

我们在我工作的地方也使用类似的东西。循环遍历系统的每个表、视图和存储过程。

CREATE PROCEDURE dbo.SP_GrantFullAccess 
    @username varchar(300)
AS

DECLARE @on varchar(300) 
DECLARE @count int
SET @count = 0

PRINT 'Granting access to user ' + @username + ' on the following objects:'

DECLARE c CURSOR FOR 
SELECT name FROM sysobjects WHERE type IN('U', 'V', 'SP', 'P') ORDER BY name
OPEN c 
FETCH NEXT FROM c INTO @on 
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @count = @count + 1
 EXEC('GRANT ALL ON [' + @on + '] TO [' + @username + ']') 
 --PRINT 'GRANT ALL ON [' + @on + '] TO ' + @username
 PRINT @on
 FETCH NEXT FROM c INTO @on 
END 
CLOSE c 
DEALLOCATE c

PRINT 'Granted access to ' + cast(@count as varchar(4)) + ' object(s).'
GO

#5

use [YourDb]
GO
exec sp_MSforeachtable @command1=
    "GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON ? TO Admins, Mgmt",
    @whereand = " and o.name like 'tbl_%'"
GO

use [YourDb]
GO
exec sp_MSforeachtable @command1=
    "GRANT REFERENCES, SELECT ON ? TO Employee, public",
    @whereand = " and o.name like 'tbl_%'"
GO

#1