msdn|MS SQL数据库怎么 批量给存储过程/函数授权呢？

【www.quanqiunao.cn--数据库安全】

在工作当中遇到一个类似这样的问题：要对数据库账户的权限进行清理、设置，其中有一个用户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里面会有一条记录,如下所示：

VCBFWEVDIE9OIGRiby5zcF9kaXNrY2FwYWNpdHlfY2FsIFRPIFRlc3QxOzwvcD48cCBhbGlnbj0="center">

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

VCZuYnNwOzEmbmJzcDtGUk9NJm5ic3A7c3lzb2JqZWN0cyZuYnNwO1dIRVJFJm5ic3A7aWQ9T0JKRUNUX0lEKE4="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

本文来源：http://www.quanqiunao.cn/anquanzhongxin/15570/