Learn more about SQL Server tools

   
   















































Grant Execute to all SQL Server Stored Procedures

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

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?

Solution

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
GO
-- 1 - db_executestoredprocedures
-- 1a - Create role
CREATE ROLE db_executestoredprocedures
GO
-- 1b - Grant permissions
GRANT EXECUTE TO db_executestoredprocedures
GO

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

Grant Execute to All Stored Procedures in SQL Server 2000

CREATE PROCEDURE dbo.spGrantExectoAllStoredProcs @user sysname
AS
/*----------------------------------------------------------------------------
-- 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
--
*/ 
SET NOCOUNT ON
-- 1 - Variable declarations
DECLARE @CMD1 varchar(8000)
DECLARE @MAXOID int
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
WHILE @MAXOID > 0
BEGIN 
 -- 6 - Initialize the variables
 SELECT @OwnerName = StoredProcOwner,
 @ObjectName = StoredProcName
 FROM #StoredProcedures
 WHERE OID = @MAXOID
 -- 7 - Build the string
 SELECT @CMD1 = 'GRANT EXEC ON ' + '[' + @OwnerName + ']' + '.' 
 + '[' + @ObjectName + ']' + ' TO ' + '[' + @user + ']'
 -- 8 - Execute the string
 -- SELECT @CMD1
 EXEC(@CMD1)
-- 9 - Decrement @MAXOID
SET @MAXOID = @MAXOID - 1
END
-- 10 - Drop the temporary table
DROP TABLE #StoredProcedures
SET NOCOUNT OFF
GO

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
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

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'

to

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

thanks

Qasim


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

William,

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

Craig,

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

sky2K1,

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.

sk


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

William,

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 A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor
Troy

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)
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME NOT LIKE 'dt_%'
       AND ROUTINE_TYPE = 'PROCEDURE'

 to:

INSERT INTO #StoredProcedures (StoredProcOwner, StoredProcName)
    SELECT ROUTINE_SCHEMA, ROUTINE_NAME
      FROM INFORMATION_SCHEMA.ROUTINES
     WHERE ROUTINE_NAME NOT LIKE 'dt_%'
       AND DATA_TYPE <> N'TABLE'
       AND (   ROUTINE_TYPE = N'PROCEDURE'
            OR ROUTINE_TYPE = N'FUNCTION')

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