sqlSQL数据库怎么批量为存储过程函数授权

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

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

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

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

则不做任何 *** 作

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

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

CREATE PROCEDURE sp_authorize_right

(

@typeAS CHAR(10),

@userAS VARCHAR(20)

)

AS

DECLARE @sqlTextVARCHAR(1000)

DECLARE @UserIdINT

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 + ''

FROMsys.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 +''

--FROMsys.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 + ''

FROMsys.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 + ''

FROMsys.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

首先，需要resource，connect

grant resource,connect to guest

然后赋予 执行权限（对每一个存储过程授权）：

grant execute on cache.xxxxxx to guest

---