sqlSQL数据库怎么批量为存储过程/函数授权呢？

数据库  /  管理员 发布于 6年前   216

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

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

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

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

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

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

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test;

GRANT EXEC ON dbo.sp_diskcapacity_cal TO Test1;

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

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

复制代码 代码如下:
Code Snippet
USE MyAssistant;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE id=OBJECT_ID(N'sp_authorize_right') AND OBJECTPROPERTY(id, 'IsProcedure') =1)
    DROP PROCEDURE sp_authorize_right;
GO
--=========================================================================================================
--        ProcedureName        :            sp_authorize_right
--        Author               :            Kerry   
--        CreateDate           :            2013-05-10               
--        Blog                 :            www.cnblogs.com/kerrycode/       
--        Description          :            将数据库的所有自定义存储过程或自定义函数赋权给某个用户(可以继续扩展)
/**********************************************************************************************************
        Parameter              :                         参数说明
***********************************************************************************************************
        @type                  :  'P'  代表存储过程 , 'F' 代表存储过程，如果需要可以扩展其它对象
        @user                  :  某个用户账户
***********************************************************************************************************
    Modified Date        Modified User        Version                    Modified Reason
***********************************************************************************************************
    2013-05-13           Kerry                 V01.00.01             排除系统存储过程和系统函数的授权处理
    2013-05-14           Kerry                 V01.00.02             增加判断,如果某个存储过程已经赋予权限
                                                                     则不做任何操作
***********************************************************************************************************/
--=========================================================================================================
CREATE PROCEDURE sp_authorize_right
(
    @type        AS   CHAR(10)    ,
    @user        AS     VARCHAR(20)
)
AS
  DECLARE @sqlTextVARCHAR(1000);
  DECLARE @UserId    INT;
SELECT @UserId = uid FROM sys.sysusers WHERE name=@user;
    IF @type = 'P'
      BEGIN
        CREATE TABLE #ProcedureName( SqlText  VARCHAR(max));
            INSERT  INTO #ProcedureName
            SELECT  'GRANT EXECUTE ON ' + p.name + ' TO ' + @user + ';'
            FROM    sys.procedures p
            WHERE   NOT EXISTS( SELECT 1
                                 FROM   sys.database_permissions r
                                 WHERE  r.major_id = p.object_id
                                        AND r.grantee_principal_id = @UserId
                                        AND r.permission_name IS NOT  NULL )
            SELECT * FROM #ProcedureName;
            --SELECT  'GRANT EXECUTE ON ' + NAME + ' TO ' +@user +';'
            --FROM    sys.procedures;
            --SELECT 'GRANT EXECUTE ON ' + [name] + ' TO ' +@user +';'
            -- FROM sys.all_objects
            --WHERE [type]='P' OR [type]='X' OR [type]='PC'
        DECLARE cr_procedure CURSOR FOR
            SELECT * FROM #ProcedureName;
        OPEN cr_procedure;
        FETCH NEXT FROM cr_procedure  INTO @sqlText;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXECUTE(@sqlText);
            FETCH NEXT FROM cr_procedure INTO @sqlText;
        END   
        CLOSE cr_procedure;
        DEALLOCATE cr_procedure;
      END
    ELSE
        IF @type='F'
           BEGIN
               CREATE TABLE #FunctionSet( functionName VARCHAR(1000));
            INSERT  INTO #FunctionSet
            SELECT  'GRANT EXEC ON ' + name + ' TO ' + @user + ';'
            FROM    sys.all_objects s
            WHERE   NOT EXISTS( SELECT 1
                                 FROM   sys.database_permissions p
                                 WHERE  p.major_id = s.object_id
                                    AND  p.grantee_principal_id = @UserId)
                    AND schema_id = SCHEMA_ID('dbo')
                    AND( s.[type] = 'FN'
                          OR s.[type] = 'AF'
                          OR s.[type] = 'FS'
                          OR s.[type] = 'FT'
                        ) ;
              SELECT * FROM #FunctionSet;
                    --SELECT 'GRANT EXEC ON ' + name + ' TO ' + @user +';' FROM sys.all_objects
                    -- WHERE schema_id =schema_id('dbo')
                    --     AND ([type]='FN' OR [type] ='AF' OR [type]='FS' OR [type]='FT' );
            INSERT  INTO #FunctionSet
            SELECT  'GRANT SELECT ON ' + name + ' TO ' + @user + ';'
            FROM    sys.all_objects s
            WHERE   NOT EXISTS( SELECT 1
                                 FROM   sys.database_permissions p
                                 WHERE  p.major_id = s.object_id
                                    AND  p.grantee_principal_id = @UserId)
                    AND schema_id = SCHEMA_ID('dbo')
                    AND( s.[type] = 'TF'
                          OR s.[type] = 'IF'
                        ) ;   
                SELECT * FROM #FunctionSet;
                --SELECT 'GRANT SELECT ON ' + name + ' TO ' + @user +';' FROM sys.all_objects
                -- WHERE schema_id =schema_id('dbo')
                --     AND ([type]='TF' OR  [type]='IF') ;        
               DECLARE cr_Function CURSOR FOR
                    SELECT functionName FROM #FunctionSet;
                OPEN cr_Function;
                FETCH NEXT FROM cr_Function INTO @sqlText;
                WHILE @@FETCH_STATUS = 0
                BEGIN   
                    PRINT(@sqlText);
                    EXEC(@sqlText);
                    FETCH NEXT FROM cr_Function INTO @sqlText;
                END
                CLOSE cr_Function;
                DEALLOCATE cr_Function;
           END
GO