Sunday 3 January 2010

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



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)
-- get next item
SELECT TOP 1 @procname = [name] FROM #tmp

-- Prepare and execute SQL statement
SET @sql = 'GRANT EXEC ON ' + @procname + ' TO ' + @login

-- do we run or print them out
IF @RunEXEC = 1
EXEC (@sql)

-- delete record from temp table
DELETE #tmp WHERE [name] = @procname

SELECT @Count = @Count + 1

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.


Steve K said...

Just what I was looking for. We almost always forget to apply permissions to our stored procs when we deploy them, so this is really a lifesaver. Thanks for the post!

Anonymous said...

Why not just simply grant exec on your schema (or even on your database).

Rob Reid said...

Because you might not want to.

There are many ways to skin a cat and this is just an example of using system views to create multiple useful commands.

You are right that you could grant execute permission on your schema or DB if every proc and UDF had the same execute level but in my system certain procs impersonate other users. Also you might have logons that shouldn't have the same execute permission as others (with a single schema) and therefore you wouldn't want to grant execute permission to the whole schema or DB.

Frits said...

Thank you for your reply!

In 'my' databases there is a 'general' database role that should have permissions on all procs and UDFs (single schema). Also this role should have select permissions (because of the use of sp_executesql in procs).

In this simple security model granting on schema level might be the easiest approach. I hope.

Rob Reid said...

As I said there are many ways to skin a cat and this is an "example" of how to use system tables to solve a solution.

It is up to you whether you want to use it OR even if you need to use it at all - from your own situation it seems you don't.

However not every system revolves around one web login connected to one database or schema.

One of my bigger systems is made up of 5 SQL DB's on the same server with multiple logins, users and special roles that certain logins have to be member of to access the tables within certain DB's.

This involves Trustworthy(ness) being set (for cross DB calls) Execute AS impersonation for times when a basic webuser HAS to do something unusual that is isn't suitable for creating a whole role for and many other logins, roles and users. If we had the resources, time and inclination we could probably set the security up on this system in a multitude of ways but at the moment it is secure as no CUD operations are done client side and are only carried out in Stored Procs with execute permission granted to the logins that require them.

It all depends on the complexity of the situation you are dealing with but if you don't need to grant execute permission to all your UDF's or PROCs in one go (or filter out those you do - OR just print out the statements) then that is fine just don't use the script.

Thanks for commenting.