复制代码 代码如下:

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