在工作當中遇到一個類似這樣的問題：要對數據庫賬戶的權限進行清理、設置，其中有一個用戶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
