用SQL Script 快速生成给数据库角色授权的数据库脚本

      平常我们在创建好一个新的数据据之后，通常都会要求给数据库创建一个角色，然后给这个角色授权。

以下是我用来给数据库角色授权的还不错的SQL Script 脚本：

 

 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 + ']' 
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 

     1. 把@RoleName变更的值修改一下。

　  2. 在Microsoft SQL Server Management Studio上运行一下，就可以在结果窗口中得到结果，挺方便的。（以文本格式显示结果，更便于Copy得到的脚本）