How can I batch authorize stored procedures/functions in a SQL database?

At work, I encountered a problem like this: to clear and set the permissions of the database account, one of the users Test, you can only have the DML (Update, insert, delete, etc.) operation permissions of the database MyAssistant, and the permission to execute database stored procedures and functions, however, DDL operations (including creating, modifying tables, and storing procedures) are not allowed ...), therefore, you need to set the Test permission for the Login Name:

1: Right-click the attributes of the login name Test.

2: Select "public" server role in the server role.

3: Select "db_datareader", "db_datawriter", and "public" from the user ing options.

At this point, you have implemented the DML operation permission. If you need to have the execution permission for stored procedures and functions, you must use the GRANT statement to GRANT permissions, the storage process and functions of a production database are combined into hundreds of thousands. If you execute them manually, it will be a hard job, and I have more than a dozen databases on hand, therefore, you must use scripts to implement the authorization process. The following is a stored procedure I wrote. The main highlight is to determine whether the stored procedure, function has been granted the EXE or SELECT permission to a user. Here we mainly use the security directory to try sys. database_permissions. For example, the database contains a stored procedure dbo. sp_authorize_right: If the stored procedure is authorized to the Test user, try sys in the directory. database_permissions contains a record, as shown below:

If I grant the EXEC permission to TEST1 for this stored procedure

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'

In fact, grantee_principal_id indicates the ID of the database subject to which permissions are granted. Therefore, I can use the two views above to determine whether the stored procedure has been granted the execution permission to the user Test or not. Likewise, this is true for functions, the stored procedure is as follows. In fact, this stored procedure can be expanded, if you have special needs.

Copy codeThe Code is as follows:
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: grant all user-defined stored procedures or functions of the database to a user (which can be expanded)
/*************************************** **************************************** ***************************
Parameter: Parameter description
**************************************** **************************************** ***************************
@ Type: 'P' indicates the stored procedure, and 'F' indicates the stored procedure. If necessary, you can expand other objects.
@ User: a user account
**************************************** **************************************** ***************************
Modified Date Modified User Version Modified Reason
**************************************** **************************************** ***************************
2013-05-13 Kerry V01.00.01 exclude the authorization of system stored procedures and system functions
2013-05-14 Kerry V01.00.02 adds judgment, if a stored procedure has been granted Permissions
No operation is performed.
**************************************** **************************************** ***************************/
-- ===================================================== ========================================================== ======================================
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
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
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