The task which I was doing today required me to prepare a SQL script which grants the user to execute the user defined functions and stored procedures. While SQL Server Management Studio allows us to set the permission by a few clicks, it is good to prepare them using the script so we can re-use them in case we have run them in the development and production databases.
A simple script to grant user with execute permission for stored procedures. It applies to all stored procedures in the selected database.
/* GRANT EXECUTE TO THE ROLE */ GRANT EXECUTE TO <user>
Next, I found out that my application needs to set permission for the user defined functions. I felt I could have spent the same amount of time to search the script to automate the process and manually wrote the script one by one. The script below can help,
/* GRANT EXECUTE TO THE USER DEFINED FUNCTIONS */ SELECT 'GRANT EXECUTE ON [' + SCHEMA_NAME(schema_id) + '].[' + [name] + '] TO MyRole;' FROM sys.objects WHERE type IN ('TF','FN');
There is another script which could help,
declare @name varchar(100), @qry varchar(2000) declare cursor cursor_temp for select name from dbo.sysobjects where OBJECTPROPERTY (id, IsProcedure) = 1 open cursor_temp fetch next from cursor_temp into @name while @@fetch_status = 0 begin set @qry = ' grant execute on [dbo].[' + @name + ' ] to [ <user_name> ]' exec (@qry) fetch next from cursor_temp into @name end close cursor_temp deallocate cursor_temp
I hope this could help.