用SQL Script 快速生成给数据库角色授权的数据库脚本
平常我们在创建好一个新的数据据之后，通常都会要求给数据库创建一个角色，然后给这个角色授权。
代码
以下是我用来给数据库角色授权的还不错的SQL Script 脚本：
代码
<!--Code highlighting produced by Actipro CodeHighlighter (freeware)http://www.CodeHighlighter.com/--> 1
2 SET NOCOUNT ON;
3
4 DECLARE @RoleName nvarchar(50)
5 SET @RoleName = 'RoleName'--你的数据库角色的名称
6
7 PRINT '
8 -- =============================================
9 -- Section I: Grant Table SELECT Permission
10 -- ============================================='
11 SELECT 'GRANT SELECT ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
12
13 PRINT '
14 -- =============================================
15 -- Section I: Grant Table INSERT Permission
16 -- ============================================='
17 SELECT 'GRANT INSERT ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
18
19 PRINT '
20 -- =============================================
21 -- Section I: Grant Table UPDATE Permission
22 -- ============================================='
23 SELECT 'GRANT UPDATE ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
24
25 PRINT '
26 -- =============================================
27 -- Section I: Grant Table DELETE Permission
28 -- ============================================='
29 SELECT 'GRANT DELETE ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']'
2 SET NOCOUNT ON;
3
4 DECLARE @RoleName nvarchar(50)
5 SET @RoleName = 'RoleName'--你的数据库角色的名称
6
7 PRINT '
8 -- =============================================
9 -- Section I: Grant Table SELECT Permission
10 -- ============================================='
11 SELECT 'GRANT SELECT ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
12
13 PRINT '
14 -- =============================================
15 -- Section I: Grant Table INSERT Permission
16 -- ============================================='
17 SELECT 'GRANT INSERT ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
18
19 PRINT '
20 -- =============================================
21 -- Section I: Grant Table UPDATE Permission
22 -- ============================================='
23 SELECT 'GRANT UPDATE ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
24
25 PRINT '
26 -- =============================================
27 -- Section I: Grant Table DELETE Permission
28 -- ============================================='
29 SELECT 'GRANT DELETE ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']'
FROM INFORMATION_SCHEMA.TABLES WHERE [TABLE_TYPE] = 'BASE TABLE' AND [TABLE_NAME] <> 'sysdiagrams'
30
31 PRINT '
32 -- =============================================
33 -- Section II: Grant View SELECT Permission
34 -- ============================================='
35 SELECT 'GRANT SELECT ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.VIEWS
36
37 PRINT '
38 -- =============================================
39 -- Section III: Grant PROCEDURE EXECUTE Permission
40 -- ============================================='
41 SELECT 'GRANT EXECUTE ON [' + [ROUTINE_NAME] + '] TO [' + @RoleName + ']'
42 FROM INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_TYPE] = 'PROCEDURE'
43 AND [ROUTINE_NAME] NOT IN (
44 'sp_upgraddiagrams'
45 ,'sp_helpdiagrams'
46 ,'sp_helpdiagramdefinition'
47 ,'sp_creatediagram'
48 ,'sp_renamediagram'
49 ,'sp_alterdiagram'
50 ,'sp_dropdiagram'
51 )
52
53 PRINT '
54 -- =============================================
55 -- Section IV: Grant FUNCTION EXECUTE Permission
56 -- ============================================='
57 --EXECUTE 是标量值函数
58 SELECT 'GRANT EXECUTE ON [' + [ROUTINE_NAME] + '] TO [' + @RoleName + ']'
59 FROM INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_TYPE] = 'FUNCTION' AND [ROUTINE_NAME] <> 'fn_diagramobjects' AND [DATA_TYPE] <> 'TABLE'
60 --SELECT 是表值函数
61 SELECT 'GRANT SELECT ON [' + [ROUTINE_NAME] + '] TO [' + @RoleName + ']'
62 FROM INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_TYPE] = 'FUNCTION' AND [ROUTINE_NAME] <> 'fn_diagramobjects' AND [DATA_TYPE] = 'TABLE'
63
64
30
31 PRINT '
32 -- =============================================
33 -- Section II: Grant View SELECT Permission
34 -- ============================================='
35 SELECT 'GRANT SELECT ON [' + [TABLE_NAME] + '] TO [' + @RoleName + ']' FROM INFORMATION_SCHEMA.VIEWS
36
37 PRINT '
38 -- =============================================
39 -- Section III: Grant PROCEDURE EXECUTE Permission
40 -- ============================================='
41 SELECT 'GRANT EXECUTE ON [' + [ROUTINE_NAME] + '] TO [' + @RoleName + ']'
42 FROM INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_TYPE] = 'PROCEDURE'
43 AND [ROUTINE_NAME] NOT IN (
44 'sp_upgraddiagrams'
45 ,'sp_helpdiagrams'
46 ,'sp_helpdiagramdefinition'
47 ,'sp_creatediagram'
48 ,'sp_renamediagram'
49 ,'sp_alterdiagram'
50 ,'sp_dropdiagram'
51 )
52
53 PRINT '
54 -- =============================================
55 -- Section IV: Grant FUNCTION EXECUTE Permission
56 -- ============================================='
57 --EXECUTE 是标量值函数
58 SELECT 'GRANT EXECUTE ON [' + [ROUTINE_NAME] + '] TO [' + @RoleName + ']'
59 FROM INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_TYPE] = 'FUNCTION' AND [ROUTINE_NAME] <> 'fn_diagramobjects' AND [DATA_TYPE] <> 'TABLE'
60 --SELECT 是表值函数
61 SELECT 'GRANT SELECT ON [' + [ROUTINE_NAME] + '] TO [' + @RoleName + ']'
62 FROM INFORMATION_SCHEMA.ROUTINES WHERE [ROUTINE_TYPE] = 'FUNCTION' AND [ROUTINE_NAME] <> 'fn_diagramobjects' AND [DATA_TYPE] = 'TABLE'
63
64
1. 把@RoleName变更的值修改一下。
2. 在Microsoft SQL Server Management Studio上运行一下，就可以在结果窗口中得到结果，挺方便的。（以文本格式显示结果，更便于Copy得到的脚本）
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16436858/viewspace-627052/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
