在工作當(dāng)中遇到一個類似這樣的問題：要對數(shù)據(jù)庫賬戶的權(quán)限進(jìn)行清理、設(shè)置，其中有一個用戶Test，只能擁有數(shù)據(jù)庫MyAssistant的DML(更新、插入、刪除等)操作權(quán)限，另外擁有執(zhí)行數(shù)據(jù)庫存儲過程、函數(shù)的權(quán)限，但是不能進(jìn)行DDL操作（包括新建、修改表、存儲過程等...），于是需要設(shè)置登錄名Test的相關(guān)權(quán)限：
1：右鍵單擊登錄名Test的屬性.
2: 在服務(wù)器角色里面選擇"public"服務(wù)器角色。
3：在用戶映射選項(xiàng)當(dāng)中，選擇"db_datareader"、"db_datawriter"、"public"三個數(shù)據(jù)庫角色成員。
此時，已經(jīng)實(shí)現(xiàn)了擁有DML操作權(quán)限，如果需要擁有存儲過程和函數(shù)的執(zhí)行權(quán)限，必須使用GRANT語句去授權(quán)，一個生產(chǎn)庫的存儲過程和函數(shù)加起來成千上百，如果手工執(zhí)行的話，那將是一個辛苦的體力活，而我手頭有十幾個庫，所以必須用腳本去實(shí)現(xiàn)授權(quán)過程。下面是我寫的一個存儲過程，亮點(diǎn)主要在于會判斷存儲過程、函數(shù)是否已經(jīng)授予了EXE或SELECT權(quán)限給某個用戶。這里主要用到了安全目錄試圖sys.database_permissions，例如，數(shù)據(jù)庫里面有個存儲過程dbo.sp_authorize_right，如果這個存儲過程授權(quán)給Test用戶了話，那么在目錄試圖sys.database_permissions里面會有一條記錄,如下所示：
如果我將該存儲過程授予EXEC權(quán)限給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'
其實(shí)grantee_principal_id代表向其授予權(quán)限的數(shù)據(jù)庫主體 ID ，所以我就能通過上面兩個視圖來判斷存儲過程是否授予執(zhí)行權(quán)限給用戶Test與否，同理，對于函數(shù)也是如此，存儲過程如下所示，其實(shí)這個存儲過程還可以擴(kuò)展，如果您有特殊的需要的話。
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 : 將數(shù)據(jù)庫的所有自定義存儲過程或自定義函數(shù)賦權(quán)給某個用戶(可以繼續(xù)擴(kuò)展)
/**********************************************************************************************************
Parameter : 參數(shù)說明
***********************************************************************************************************
@type : 'P' 代表存儲過程 , 'F' 代表存儲過程，如果需要可以擴(kuò)展其它對象
@user : 某個用戶賬戶
***********************************************************************************************************
Modified Date Modified User Version Modified Reason
***********************************************************************************************************
2013-05-13 Kerry V01.00.01 排除系統(tǒng)存儲過程和系統(tǒng)函數(shù)的授權(quán)處理
2013-05-14 Kerry V01.00.02 增加判斷,如果某個存儲過程已經(jīng)賦予權(quán)限
則不做任何操作
***********************************************************************************************************/
--=========================================================================================================
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
