In the work encountered a similar problem: to the Database account permissions to clean up, set up, which has a user test, can only have the database myassistant DML (UPDATE, INSERT, delete, and so on) operation permissions, in addition to the execution of database stored procedures, functions, permissions, However, DDL operations cannot be performed (including new, modified tables, stored procedures, etc. ...). , you need to set permissions on the logon name test:
1: Right-click the properties of the login name test.
2: Select the "Public" server role inside the server role.
3: In the User mapping option, select the "db_datareader", "db_datawriter", "public" three database role members.
At this point, you have implemented the right to have DML operations, if you need to have execute permissions on stored procedures and functions, you must use the GRANT statement to authorize a production library with hundreds of stored procedures and functions, which, if executed manually, would be a hard labor, and I have more than 10 libraries on hand, So you have to use a script to implement the authorization process. Here is a stored procedure I wrote, the highlight is to determine whether the stored procedure, function has been granted EXE or SELECT permissions to a user. The main use of the security directory is to try to sys.database_permissions, for example, there is a stored procedure in the database dbo.sp_authorize_right, if the stored procedure is authorized to the test user, Then there will be a record in the directory attempt sys.database_permissions, as follows:
If I grant EXEC permission to TEST1 for this stored procedure, then
GRANT EXEC dbo.sp_diskcapacity_cal to Test;
GRANT EXEC dbo.sp_diskcapacity_cal to Test1;
SELECT * from sys.sysusers WHERE name = ' Test ' OR ' name = ' Test1 '
In fact, grantee_principal_id represents the database principal ID to which permissions are granted, so I can determine whether the stored procedure grants execute permissions to the user test or not by the two views above, as well as for functions, as shown in the stored procedure. In fact, this stored procedure can also be extended if you have special needs.
Copy Code code 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: Assign all custom stored procedures or custom functions of a database to a user (you can continue to expand)
/**********************************************************************************************************
Parameter: Parameter description
***********************************************************************************************************
@type: ' P ' represents the stored procedure, ' F ' represents the stored procedure, and if necessary can extend other objects
@user: A user account
***********************************************************************************************************
Modified Date Modified User Version Modified Reason
***********************************************************************************************************
2013-05-13 Kerry V01.00.01 excludes authorization processing for system stored procedures and system functions
2013-05-14 Kerry V01.00.02 Increase judgment if a stored procedure has been given permission
No action is done
***********************************************************************************************************/
--=========================================================================================================
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 ' + 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 ' + 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 ')
--([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
