Grant Execute Permission to all Stored Procedures and Functions

How to grant execute permission to all your stored procedures and functions

Another useful stored procedure I have in my toolkit that I find myself using over and over on any system that I work on is the following proc that allows me to grant execute permission to all stored procedures and user defined functions within a database to a particular logon.

Sometimes I find myself creating multiple stored procedures or functions in one go, either through a CRUD proc generator script or just manually but without the necessary execute permissions added to each procedure or function they will fail to execute. Therefore its very handy if you have forgotten to add the GRANT EXECUTE statements as you went along to do it all in one batch.

The procedure makes use of the sysobjects system view to return a list of all the stored procedures and user defined functions within the database the procedure is called from. I then loop through each one and generate the necessary SQL to apply EXECUTE permission to that proc or UDF to the supplied logon.

I have also added a flag which allows you to print all the GRANT EXECUTE statements without running them so that you can check the output before running it.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_grant_execute_permission] 

 @login VARCHAR(50) = 'mylogon',
 @RunEXEC BIT = 1 --1 will run the statements, 0 = Prints them

AS

SET NOCOUNT ON

DECLARE @procname VARCHAR(50),
  @Rows INT,
  @Count INT

-- Populate the temporary table with user defined stored procs and functions
SELECT [name] INTO #tmp 
FROM sysobjects 
WHERE (xtype = 'P') OR  (xtype = 'FN' OR xtype = 'TF')

SELECT @Rows = @@ROWCOUNT, @Count = 0


-- Iterate through each of the rows on the table
WHILE (@Count < @Rows)
BEGIN
 -- get next item
 SELECT TOP 1 @procname = [name] FROM #tmp

 -- Prepare and execute SQL statement
 DECLARE @sql VARCHAR(255)
 SET @sql = 'GRANT EXEC ON ' + @procname + ' TO ' + @login
 
 -- do we run or print them out
 IF @RunEXEC = 1
   EXEC (@sql)
 ELSE
   PRINT @SQL
 
 -- delete record from temp table 
 DELETE #tmp WHERE [name] = @procname
 
 SELECT @Count = @Count + 1
END

You call it simply like so:

EXEC usp_sql_grant_execute_permission 'mylogin'

It's one of those simple procedures that are very handy to have and save a lot of time. Its very quick to run so if I ever come across any permission denied errors on my development system I just run this procedure first to ensure all my procedures and functions have the necessary execute permissions to run correctly.

Find Text Inside a Stored Procedure or Used Defined Function

How to find text inside a stored procedure or user defined function

One of the most useful stored procedures I have in my toolkit that I find myself using over and over on any system that I work on is the following procedure that allows me to return a list of stored procedures and user defined functions that contain a particular string of text.

I maybe looking for all procs or UDF's that contain a table or view name or need a list of all procs that do have SET NOCOUNT ON so that I can find those that don't or I may just be looking for a particular variable name or comment within all my procs and functions.

It makes use of the system view syscomments which stores all the text within the stored procedures and user defined functions contained within your SQL Server database. This is just another example of how having knowledge of the system views is a very useful skill to know.

The code is below.

CREATE PROCEDURE [dbo].[usp_sql_find_string_in_proc_or_udf]

@FindStr AS VARCHAR(500)

AS

SET NOCOUNT ON

SELECT DISTINCT NAME AS [NAME],
 CASE  WHEN TYPE ='P' THEN 'PROCEDURE'
     WHEN TYPE IN('FN', 'IF','TF') THEN 'FUNCTION'
 END AS OBJECTTYPE
FROM SYSCOMMENTS as comm
JOIN sysobjects as obj
ON comm.id = obj.id and obj.type IN ('P','FN', 'IF', 'TF')
WHERE lower(TEXT) LIKE '%' + ltrim(rtrim(lower(@FindStr))) + '%'

You call it simply like so:

EXEC dbo.usp_sql_find_string_in_proc_or_udf 'SOME_TABLE'

EXEC dbo.usp_sql_find_string_in_proc_or_udf '@ErrorVar varchar(100)'

It's one of those simple procedures that are very handy to have and save a lot of time. Being able to find a piece of text within all your stored procedures and functions quickly could literally save you hours of hunting about on a large system.