MS SQL数据库如何 批量给存储过程/函数授权呢？

在工作当中遇见一个类似如此的问题：要对数据库账户的权限进行清理、设置，其中有一个用户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.sysusersWHERE name ='Test' OR name ='Test1'

其实grantee_principal_id代表向其授与权限的数据库主体 ID ，所以我就能通过上面两个视图来判断存储过程是不是授与实行权限给用户Test与否，同理，对于函数也是这样，存储过程如下所示，其实这个存储过程还可以扩展，假如你有特殊的需要的话。

Code Snippet

U百度竞价推广yAssistant;

GO

SETANSI_NULLSON;

GO

SETQUOTED_IDENTIFIERON

GO

IFEXISTSANDOBJECTPROPERTY=1)

DROPPROCEDUREsp_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 DateModified UserVersionModified Reason

***********************************************************************************************************

2013-05-13KerryV01.00.01排除系统存储过程和系统函数的授权处置

2013-05-14KerryV01.00.02增加判断,假如某个存储过程已经赋予权限

则不做任何操作

***********************************************************************************************************/

--=========================================================================================================

CREATEPROCEDUREsp_authorize_right

,

@userASVARCHAR

)

AS

DECLARE@sqlTextVARCHAR;

DECLARE@UserIdINT;

SELECT@UserId=uidFROMsys.sysusersWHEREname=@user;

IF@type='P'

BEGIN

CREATETABLE#ProcedureName);

INSERTINTO#ProcedureName

SELECT'GRANT EXECUTE ON '+p.name+' TO '+@user+';'

FROMsys.proceduresp

WHERENOTEXISTS

SELECT*FROM#ProcedureName;

--SELECT'GRANT EXECUTE ON ' + NAME + ' TO ' +@user +';'

--FROMsys.procedures;

--SELECT 'GRANT EXECUTE ON ' + [name] + ' TO ' +@user +';'

-- FROM sys.all_objects

--WHERE [type]='P' OR [type]='X' OR [type]='PC'

DECLAREcr_procedureCURSORFOR

SELECT*FROM#ProcedureName;

OPENcr_procedure;

FETCHNEXTFROMcr_procedureINTO@sqlText;

WHILE@@FETCH_STATUS=0

BEGIN

EXECUTE;

FETCHNEXTFROMcr_procedureINTO@sqlText;

END

CLOSEcr_procedure;

DEALLOCATEcr_procedure;

END

ELSE

IF@type='F'

BEGIN

CREATETABLE#FunctionSet);

INSERTINTO#FunctionSet

SELECT'GRANT EXEC ON '+name+' TO '+@user+';'

FROMsys.all_objectss

WHERENOTEXISTS

ANDschema_id=SCHEMA_ID

AND;

SELECT*FROM#FunctionSet;

--SELECT 'GRANT EXEC ON ' + name + ' TO ' + @user +';' FROM sys.all_objects

-- WHERE schema_id =schema_id

--AND ;

INSERTINTO#FunctionSet

SELECT'GRANT SELECT ON '+name+' TO '+@user+';'

FROMsys.all_objectss

WHERENOTEXISTS

ANDschema_id=SCHEMA_ID

AND;

SELECT*FROM#FunctionSet;

--SELECT 'GRANT SELECT ON ' + name + ' TO ' + @user +';' FROM sys.all_objects

-- WHERE schema_id =schema_id

--AND ;

DECLAREcr_FunctionCURSORFOR

SELECTfunctionNameFROM#FunctionSet;

OPENcr_Function;

FETCHNEXTFROMcr_FunctionINTO@sqlText;

WHILE@@FETCH_STATUS=0

BEGIN

PRINT;

EXEC;

FETCHNEXTFROMcr_FunctionINTO@sqlText;

END

CLOSEcr_Function;

DEALLOCATEcr_Function;

END

GO

TAG标签：存储过程(1)

转载请说明来源于谷普下载站（https://www.muerya.com）

本文地址：https://www.muerya.com/news/2686.html

郑重声明：文章来源于网络作为参考，本站仅用于分享不存储任何下载资源,如果网站中图片和文字侵犯了您的版权，请联系我们处理！邮箱3450399331@qq.com