sqlSQL數據庫怎樣批量為存儲進程/函數受權呢？。本站提示廣大學習愛好者：（sqlSQL數據庫怎樣批量為存儲進程/函數受權呢？）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlSQL數據庫怎樣批量為存儲進程/函數受權呢？正文
在任務傍邊碰到一個相似如許的成績：要對數據庫賬戶的權限停止清算、設置，個中有一個用戶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
