Grant EXECUTE permission to users

,

This procedure will assist in granting execute rights to a database user to all the stored procedures in a database
The parameters to this procedure are the databasename and the username to whom EXECUTE rights need to be given to

The assumption here is that the user already has access
rights to the database mentioned in the parameter list

Depending on the coding standards in your company you can
replace the string s_% with any other text for stored
procedure names

/**************************************************************/
/* This procedure will assist in granting execute rights to a */
/* database user to all the stored procedures in a database   */
/* The parameters to this procedure are the databasename and  */
/* the username to whom EXECUTE rights need to be given to    */
/*							      */
/* The assumption here is that the user already has access    */
/* rights to the database mentioned in the parameter list     */
/* 							      */
/* Depending on the coding standards in your company you can  */
/* replace the string s_% with any other text for stored      */
/* procedure names					      */
/*							      */
/* usage : this procedure needs to be compiled in the database */
/* in which the stored procedures needs to be granted access  */
/**************************************************************/

-- Grant execute permission on procs to application user
IF EXISTS (SELECT name 
	   FROM   sysobjects 
	   WHERE  name = N'usp_grant_permissions' 
	   AND 	  type = 'P')
    DROP PROCEDURE usp_grant_permissions
GO

CREATE PROCEDURE usp_grant_permissions 	@UserName varchar(100)
AS
SET NOCOUNT ON
declare @temp_table table(row_count int identity(1,1), proc_name varchar(200))
declare
@proc_name	varchar(200),
@v_grant_stmt	varchar(8000),
@stmt		varchar(8000),
@cur_stmt	varchar(8000),
@row_count	int,
@counter	int


insert into @temp_table(proc_name) select name from sysobjects where xtype = 'P' and name like 's_%'

select @row_count = count(*) from @temp_table

set @counter = 1

while @counter <= @row_count
begin
		
			select @proc_name = proc_name from @temp_table where row_count = @counter
			set @v_grant_stmt = 'GRANT EXECUTE on ' + @proc_name  + ' to ' + @UserName
			exec(@v_grant_stmt)

set @counter = @counter + 1
end

Rate

Share

Share

Rate