在工作當中遇到一個類似這樣的問題：要對數據庫賬戶的權限進行清理、設置，其中有一個用戶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
您可能感興趣的文章:- 一個查看MSSQLServer數據庫空間使用情況的存儲過程 SpaceUsed
- sqlserver 復制表 復制數據庫存儲過程的方法
- sql 判斷數據庫,表，存儲過程等是否存在的代碼
- mysql 查詢數據庫中的存儲過程與函數的語句
- SQLserver 數據庫危險存儲過程刪除與恢復方法
- MSSQL MySQL 數據庫分頁(存儲過程)
- SQL Server中通過擴展存儲過程實現數據庫的遠程備份與恢復
- mysql 導入導出數據庫以及函數、存儲過程的介紹
- MSSQL監控數據庫的DDL操作(創建，修改，刪除存儲過程，創建，修改，刪除表等)
- SQL數據庫存儲過程示例解析
