Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Audit with SQL Compliance Manager and SQL Secure

How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS

Are You Making the Right Choices for SQL Server HA?

SQL Server Security Essentials

Implementing a SANLess SQL Server Cluster in Under an Hour

Grant Execute to all SQL Server Stored Procedures

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (16)   |   Related Tips: More > Stored Procedures

With SQL Server 2000 no default server, database or application role was available to be able to execute all stored procedures.  With SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2 has this changed with all of the new security features?  If not, what options do I have to grant execute rights to the needed database roles?


Unfortunately, with all of the security changes in SQL Server 2005, SQL Server 2008 and SQL Server 2008 R2, no system role is available to execute all stored procedures in a given database.  This is the same behavior as with SQL Server 2000.  As such, let's take a look at the needed code for SQL Server 2008 R2, SQL Server 2008, SQL Server 2005 and SQL Server 2000.

Grant Execute to All Stored Procedures in SQL Server 2008 R2, SQL Server 2008 and SQL Server 2005

USE DatabaseName
-- 1 - db_executestoredprocedures
-- 1a - Create role
CREATE ROLE db_executestoredprocedures
-- 1b - Grant permissions
GRANT EXECUTE TO db_executestoredprocedures

-- 2 - db_selecttablevaluedfunctions
-- 2a - Create role
CREATE ROLE db_selecttablevaluedfunctions
-- 2 - Create permissions
DECLARE @Function_Name nvarchar(250);
DECLARE @CMDEXEC1 nvarchar(2000);
SELECT [name]
FROM sys.objects
WHERE Type = 'TF'
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @Function_Name
 SET @CMDEXEC1 = 'GRANT SELECT ON [' + @Function_Name + '] TO db_selecttablevaluedfunctions;'
 FETCH NEXT FROM db_cursor INTO @Function_Name
CLOSE db_cursor   
DEALLOCATE db_cursor

Grant Execute to All Stored Procedures in SQL Server 2000

CREATE PROCEDURE dbo.spGrantExectoAllStoredProcs @user sysname
-- Object Name: spGrantExectoAllStoredProcs
-- Author: Edgewood Solutions
-- Development Date: 03.19.2007
-- Called By: TBD
-- Description: Issue GRANT EXEC statement for all stored procedures 
-- based on the user name that is passed in to this stored procedure
-- Project: SQL Server Security
-- Database: User defined databases 
-- Business Process: SQL Server Security
-- Num | CRF ID | Date Modified | Developer | Description
-- 001  | N\A     | 03.15.2007    | Edgewood | Original code for the GRANT 
-- EXEC process
-- 1 - Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @OwnerName varchar(128)
DECLARE @ObjectName varchar(128)
-- 2 - Create temporary table
CREATE TABLE #StoredProcedures
(OID int IDENTITY (1,1),
StoredProcOwner varchar(128) NOT NULL,
StoredProcName varchar(128) NOT NULL)
-- 3 - Populate temporary table
INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT u.[Name], o.[Name]
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P'
AND o.[Name] NOT LIKE 'dt_%'
-- 4 - Capture the @MAXOID value
SELECT @MAXOID = MAX(OID) FROM #StoredProcedures
-- 5 - WHILE loop
 -- 6 - Initialize the variables
 SELECT @OwnerName = StoredProcOwner,
 @ObjectName = StoredProcName
 FROM #StoredProcedures
 -- 7 - Build the string
 SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' 
 + '[' + @ObjectName + ']' + ' TO ' + '[' + @user + ']'
 -- 8 - Execute the string
-- 9 - Decrement @MAXOID
-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures

The code above can be used when a new group needs to be granted rights to all stored procedures or you are working through a migration when rights need to be granted.  Based on your naming convention or creation date, the query used in both sets of code can be modify to include the needed objects. 

With all of this being said, the best approach to address this need is to explicitly grant execute rights to stored procedures as the stored procedures are created and migrated to the production environment.  Unfortunately, if you are inheriting a SQL Server environment, you may not be that lucky.  Then the code above can come in handy to streamline the process.

Since this tip references granting stored procedures, the value of stored procedure based access to SQL Server data cannot be overlooked.  As such, stored procedure based access to SQL Server from your front end applications offers the following benefits:

  • Security based on the object that can be assigned rights with all business rules incorporated
  • No direct access to tables or views
  • Ability to call the stored procedure from any piece of code (ASP.NET, VB.NET, C#, CFMX, Job, etc.) to have a consistent set of rules executed
  • Change a single piece of code to streamline the code migration process
Next Steps

Last Update: 4/10/2012

About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as a SQL Server Consultant at Edgewood Solutions, co-founder of MSSQLTips.com as well as Baltimore SSUG co-leader.

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Saturday, December 07, 2013 - 7:31:02 AM - bandsari Read The Tip
Hi sir I like add new database user and some permission same select on some table and I must write stored procedure Thanks a lot Yours sincerely Bandsari

Wednesday, July 03, 2013 - 11:15:53 PM - xexex Read The Tip

It seem that the statement of SQL 2000 has not been updated yet. I've changed the line from

WHERE o.Type = 'P'


WHERE (o.Type = 'P' or o.Type = 'FN')

so it will grant privileges to all Function too.

Friday, October 05, 2012 - 10:54:53 AM - QASIM Read The Tip

very nice tip. it really worked for me



Monday, May 07, 2012 - 10:50:13 AM - Jeremy Kadlec Read The Tip


Thank you for the post.  I have updated the tip.

Thank you,
Jeremy Kadlec

Monday, May 07, 2012 - 10:48:56 AM - Jeremy Kadlec Read The Tip


Thank you for the post.  I think the code you are reference is from the SQL Server 2000 section in this tip.  I think the two catalog views you posted in your comment were not accessible in SQL Server 2000.  If you are using SQL Server 2008, as an example, you could change the SQL Server 2000 code as you pointed out to work with SQL Server 2008 or use the code in the section above intended for SQL Server 2008.

I hope that makes sense.  Either way, I hopefully you have your permissions sorted out.

Thank you,
Jeremy Kadlec

Friday, April 27, 2012 - 9:14:52 AM - William C. DiGiacomo Read The Tip

IMHO Step #7 could be a bit earier to read like this.  I'd have to go back and check the specification but perhaps we need braces on the @user too. ;)

SELECT @CMD1 = 'GRANT EXEC ON [' + @OwnerName + '].[' + @ObjectName + '] TO [' + @user + ']'

Friday, April 27, 2012 - 12:44:34 AM - Craig Read The Tip

This doesn't actually handle stored procedures which are within schema's.

To handle this, I had to change step 3 to the following:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
SELECT  sys.schemas.name, sys.objects.name
FROM    sys.objects 
INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.type = 'P'

Thursday, April 19, 2012 - 1:38:18 PM - Glen Read The Tip

Actually the best way to grant execute on the schema instead of the individual objects.  If all objects are in dbo then just grant execute on dbo to the database role and put the login in the role.  If you have objects that need different permissions or two groups of users that need different permissions then put the objects in separate schema' and grant appropriate permissions on them to two different roles and add the users to the appropriate role.  As long as all the schema are owned by the same user (preferably db_owner) there should be no other permissions needed.



Monday, March 05, 2012 - 6:38:47 PM - Jeremy Kadlec Read The Tip


What version of SQL Server and Excel are you using?

Are the objects that you want to grant read-only rights to in the master database or a user defined database?

Let me know the answers to these two items and I will see if I can point you in the right direction.

Thank you,
Jeremy Kadlec

Monday, March 05, 2012 - 4:21:20 PM - sky2k1 Read The Tip

I am trying to create a minimum privilege (read only) role which can select from a few tables, through ODBC for an application. When I do this though and test the ODBC using EXCEL, I can see several sys views and also dm views and master db objects.

How can I tie all of these down without individually 'rvoking' the select access to these on the role?

Can I create a role which by default will do this? See only the tables I allow it to and NOTHING else.

Thanks in advance.


Thursday, January 20, 2011 - 7:57:36 PM - Jeremy Kadlec Read The Tip


Thank you for the improvements and sharing your items with the community.

I plan on updating this tip or creating another tip to outline options in SQL Server 2008.

Thank you,
Jeremy Kadlec

Wednesday, January 19, 2011 - 3:20:29 PM - WIlliam C. DiGiacomo Read The Tip

There are two other areas that I improved on as follows:

1.  When building the string the @user var should be braced to better handle users/roles with dashes, spaces, etc.

...' TO [' + @user + ']'

2.  The Data Type of the procedure/function is needed to detrmine if we are granting EXECUTE or SELECT (e.g. TABLE means GRANT SELECT).

Monday, June 21, 2010 - 11:15:21 PM - uuandem Read The Tip

 A very handy tip.

Wednesday, March 11, 2009 - 8:57:38 AM - troytabor Read The Tip
VERY easy for SQL Server 2005. 
CREATE ROLE db_executor

GRANT EXECUTE TO db_executor

Monday, November 24, 2008 - 6:19:12 PM - sgartner Read The Tip

 The stored procedure as written will only grant privileges to stored procedures and not stored functions.  To grant to both types change section 3's insert from:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)


INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)

Sunday, September 21, 2008 - 4:40:43 PM - wtubin Read The Tip

Good tips, but when you grant the permission to windows authentication users, need to add square branket for the user when pass the parameter. also the sp will need the following change:

SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' + '[' + @ObjectName + ']' + ' TO [' + @user +']'

Sponsor Information