MS SQL 批量给存储过程/函数授权

MS SQL 批量给存储过程/函数授权

在工作当中遇到一个类似这样的问题:要对数据库账户的权限进行清理、设置,其中有一个用户Test,只能拥有数据库MyAssistant的DML(更新、插入、删除等)操作权限,另外拥有执行数据库存储过程、函数的权限,但是不能进行DDL操作(包括新建、修改表、存储过程等…),于是需要设置登录名Test的相关权限:

1:右键单击登录名Test的属性.

clip_image002[6]

2: 在服务器角色里面选择”public”服务器角色。

clip_image004

3:在用户映射选项当中,选择”db_datareader”、”db_datawriter”、”public”三个数据库角色成员。

clip_image006

此时,已经实现了拥有DML操作权限,如果需要拥有存储过程和函数的执行权限,必须使用GRANT语句去授权,一个生产库的存储过程和函数加起来成千上百,如果手工执行的话,那将是一个辛苦的体力活,而我手头有十几个库,所以必须用脚本去实现授权过程。下面是我写的一个存储过程,亮点主要在于会判断存储过程、函数是否已经授予了EXE或SELECT权限给某个用户。这里主要用到了安全目录试图sys.database_permissions,例如,数据库里面有个存储过程dbo.sp_authorize_right,如果这个存储过程授权给Test用户了话,那么在目录试图sys.database_permissions里面会有一条记录,如下所示:

image

如果我将该存储过程授予EXEC权限给TEST1,那么

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test;

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test1;

image

SELECT * FROM sys.sysusers WHERE name =’Test’ OR name =’Test1′

image

其实grantee_principal_id代表向其授予权限的数据库主体 ID ,所以我就能通过上面两个视图来判断存储过程是否授予执行权限给用户Test与否,同理,对于函数也是如此,存储过程如下所示,其实这个存储过程还可以扩展,如果您有特殊的需要的话。

Code Snippet
  1. USE MyAssistant;
  2. GO
  3. SET ANSI_NULLS ON;
  4. GO
  5. SET QUOTED_IDENTIFIER ON
  6. GO
  7. IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N’sp_authorize_right’) AND OBJECTPROPERTY(id,’IsProcedure’) =1)
  8.     DROP PROCEDURE sp_authorize_right;
  9. GO
  10. –=========================================================================================================
  11. —        ProcedureName        :            sp_authorize_right
  12. —        Author               :            Kerry
  13. —        CreateDate           :            2013-05-10
  14. —        Blog                 :            www.cnblogs.com/kerrycode/
  15. —        Description          :            将数据库的所有自定义存储过程或自定义函数赋权给某个用户(可以继续扩展)
  16. /**********************************************************************************************************
  17.         Parameter              :                         参数说明
  18. ***********************************************************************************************************
  19.         @type                  :  ‘P’  代表存储过程 , ‘F’ 代表存储过程,如果需要可以扩展其它对象
  20.         @user                  :  某个用户账户
  21. ***********************************************************************************************************
  22.     Modified Date        Modified User        Version                    Modified Reason
  23. ***********************************************************************************************************
  24.     2013-05-13           Kerry                 V01.00.01             排除系统存储过程和系统函数的授权处理
  25.     2013-05-14           Kerry                 V01.00.02             增加判断,如果某个存储过程已经赋予权限
  26.                                                                      则不做任何操作
  27. ***********************************************************************************************************/
  28. –=========================================================================================================
  29. CREATE PROCEDURE sp_authorize_right
  30. (
  31.     @type        AS   CHAR(10)    ,
  32.     @user        AS     VARCHAR(20)
  33. )
  34. AS
  35.   DECLARE @sqlTextVARCHAR(1000);
  36.   DECLARE @UserId    INT;
  37. SELECT @UserId = uid FROM sys.sysusers WHERE name=@user;
  38.     IF @type = ‘P’
  39.       BEGIN
  40.         CREATE TABLE #ProcedureName( SqlText  VARCHAR(max));
  41.             INSERT  INTO #ProcedureName
  42.             SELECT  ‘GRANT EXECUTE ON ‘ + p.name + ‘ TO ‘ + @user + ‘;’
  43.             FROM    sys.procedures p
  44.             WHERE   NOT EXISTS( SELECT 1
  45.                                  FROM   sys.database_permissions r
  46.                                  WHERE  r.major_id = p.object_id
  47.                                         AND r.grantee_principal_id = @UserId
  48.                                         AND r.permission_name IS NOT  NULL )
  49.             SELECT * FROM #ProcedureName;
  50.             –SELECT  ‘GRANT EXECUTE ON ‘ + NAME + ‘ TO ‘ +@user +’;’
  51.             –FROM    sys.procedures;
  52.             –SELECT ‘GRANT EXECUTE ON ‘ + [name] + ‘ TO ‘ +@user +’;’
  53.             — FROM sys.all_objects
  54.             –WHERE [type]=’P’ OR [type]=’X’ OR [type]=’PC’
  55.         DECLARE cr_procedure CURSOR FOR
  56.             SELECT * FROM #ProcedureName;
  57.         OPEN cr_procedure;
  58.         FETCH NEXT FROM cr_procedure  INTO @sqlText;
  59.         WHILE @@FETCH_STATUS = 0
  60.         BEGIN
  61.             EXECUTE(@sqlText);
  62.             FETCH NEXT FROM cr_procedure INTO @sqlText;
  63.         END
  64.         CLOSE cr_procedure;
  65.         DEALLOCATE cr_procedure;
  66.       END
  67.     ELSE
  68.         IF @type=’F’
  69.            BEGIN
  70.                CREATE TABLE #FunctionSet( functionName VARCHAR(1000));
  71.             INSERT  INTO #FunctionSet
  72.             SELECT  ‘GRANT EXEC ON ‘ + name + ‘ TO ‘ + @user + ‘;’
  73.             FROM    sys.all_objects s
  74.             WHERE   NOT EXISTS( SELECT 1
  75.                                  FROM   sys.database_permissions p
  76.                                  WHERE  p.major_id = s.object_id
  77.                                     AND  p.grantee_principal_id = @UserId)
  78.                     AND schema_id = SCHEMA_ID(‘dbo’)
  79.                     AND( s.[type] = ‘FN’
  80.                           OR s.[type] = ‘AF’
  81.                           OR s.[type] = ‘FS’
  82.                           OR s.[type] = ‘FT’
  83.                         ) ;
  84.               SELECT * FROM #FunctionSet;
  85.                     –SELECT ‘GRANT EXEC ON ‘ + name + ‘ TO ‘ + @user +’;’ FROM sys.all_objects
  86.                     — WHERE schema_id =schema_id(‘dbo’)
  87.                     —     AND ([type]=’FN’ OR [type] =’AF’ OR [type]=’FS’ OR [type]=’FT’ );
  88.             INSERT  INTO #FunctionSet
  89.             SELECT  ‘GRANT SELECT ON ‘ + name + ‘ TO ‘ + @user + ‘;’
  90.             FROM    sys.all_objects s
  91.             WHERE   NOT EXISTS( SELECT 1
  92.                                  FROM   sys.database_permissions p
  93.                                  WHERE  p.major_id = s.object_id
  94.                                     AND  p.grantee_principal_id = @UserId)
  95.                     AND schema_id = SCHEMA_ID(‘dbo’)
  96.                     AND( s.[type] = ‘TF’
  97.                           OR s.[type] = ‘IF’
  98.                         ) ;
  99.                 SELECT * FROM #FunctionSet;
  100.                 –SELECT ‘GRANT SELECT ON ‘ + name + ‘ TO ‘ + @user +’;’ FROM sys.all_objects
  101.                 — WHERE schema_id =schema_id(‘dbo’)
  102.                 —     AND ([type]=’TF’ OR  [type]=’IF’) ;
  103.                DECLARE cr_Function CURSOR FOR
  104.                     SELECT functionName FROM #FunctionSet;
  105.                 OPEN cr_Function;
  106.                 FETCH NEXT FROM cr_Function INTO @sqlText;
  107.                 WHILE @@FETCH_STATUS = 0
  108.                 BEGIN
  109.                     PRINT(@sqlText);
  110.                     EXEC(@sqlText);
  111.                     FETCH NEXT FROM cr_Function INTO @sqlText;
  112.                 END
  113.                 CLOSE cr_Function;
  114.                 DEALLOCATE cr_Function;
  115.            END
  116. GO

标签