首页 / 知识

关于sql server：实现SQL脚本的最佳方法是什么，该脚本将对数据库中所有用户表的数据库角色授予权限？

2023-04-14 06:23:00

What's the best way to implement a SQL script that will grant permissions to a database role on all the user tables in a database?

实现SQL脚本的最佳方法是什么，该脚本将授予数据库角色对数据库中所有用户表的选择，引用，插入，更新和删除权限？

理想情况下，此脚本可以多次运行，因为新表已添加到数据库中。 SQL Server Management Studio会为单个数据库对象生成脚本，但是我正在寻找更多的"即发即弃"脚本。

我敢肯定有一种更简单的方法，但是您可以遍历数据库中的sysobjects表，并向存在的任何用户表对象授予权限。每当添加新表时，您都可以多次运行该命令。

有一个未记录的MS过程，称为sp_MSforeachtable，您可以使用它，该过程肯定在2000年和2005年。

要授予选择权限，用法是：

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

要授予其他权限，请为每个权限添加一条新语句，或仅将其添加到命令中，如下所示：

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

稍作调整后，也有可能将角色名称也转换为参数。

齐默尔曼博士在这里正确。我希望编写一种存储过程，该存储过程具有游标在用户对象之间循环，并使用立即执行来影响授予。像这样的东西：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

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

然后您可以开除并忘记：

1	EXEC sp_grantastic

1
2
3
4
5
6
7
8
9
10
11
12
13

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

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

脚本数据库权限数据