x

Grant execute permission to all stored procedures

Is there an easy way to grant execute permissions for a user to all stored procedures at once rather than doing each one individually?

more ▼

asked Dec 08, 2009 at 01:29 PM in Default

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

FYI

GRANT EXECUTE TO UserOrRoleName;

This will grant execute permission for all the objects to which this permission could be applied. Not just for the stored procedures but the scalar and aggregate functions, synonyms and CLR types too. This should be kept in mind.

more ▼

answered Dec 08, 2009 at 06:02 PM

dillinzser gravatar image

dillinzser
91 1 1 3

(comments are locked)
10|1200 characters needed characters left

To grant permissions for a user or role to execute any procedure in a database:

USE MyDatabase
GRANT EXECUTE TO UserOrRoleName;

To grant permissions for a user or role to execute any procedure in any database:

EXEC sp_msforeachdb 'USE ?; GRANT EXECUTE TO UserOrRoleName' 
more ▼

answered Dec 08, 2009 at 01:35 PM

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

Excellent answer, but note that sp_msforeachdb is an undocumented procedure. I use it all the time, but people who use it should be aware of that.

Using the sp_msforeachdb solution as written will grant execute on the master database, with all that implies.
Dec 08, 2009 at 04:26 PM TimothyAWiseman
Very good points. I agree with your concerns. I guess I should have noted that although sp_msforeachdb will work, it should be used with caution and only by someone who understands the full impact and risks.
Dec 08, 2009 at 06:21 PM Tom Staab
(comments are locked)
10|1200 characters needed characters left

Depending on your schema structure, you could grant execute on a schema's procs i.e.

use MyDatabase go GRANT EXECUTE ON SCHEMA::[dbo] TO MyUser go 
more ▼

answered Dec 08, 2009 at 01:38 PM

Kev Riley gravatar image

Kev Riley ♦♦
54.3k 47 49 76

(comments are locked)
10|1200 characters needed characters left

If you are using SQL Server 2005 or 2008 and you want always grant EXECUTE permission to a stored procedure to some users(roles) and do not want to explicitly grat the rights, you can create a DDL trigger for such purpose. As example belows automatically grants EXECUTE permission to [public] on every newly created store.

You can easily modify the trigger to grant this permission to particular users or only grant permissions to stored proc in particular schema etc.

CREATE TRIGGER trg_GrantExecuteToPublicOnStoredProcs ON DATABASE AFTER CREATE_PROCEDURE AS BEGIN  DECLARE @eventData as xml  DECLARE @schemaName nvarchar(128)  DECLARE @objectName nvarchar(128)  DECLARE @sql nvarchar(4000)
SET @eventData = EVENTDATA()
SELECT 
    @schemaName = @eventData.value('/EVENT_INSTANCE[1]/SchemaName[1]', 'nvarchar(128)'),
    @objectName = @eventData.value('/EVENT_INSTANCE[1]/ObjectName[1]', 'nvarchar(128)')

SELECT @sql = 'GRANT EXECUTE ON [' + @schemaName + '].[' + @objectName + '] TO [public]'
EXEC sp_executesql @sql
END
more ▼

answered Dec 13, 2009 at 01:30 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

If there's no simplier way you can iterate through all the stored procedures in the db and grant the permissions on them. Here's an example:

-- Grant execute on all stored procedures in the current database
DECLARE @name nvarchar(128)
DECLARE procCursor CURSOR FOR SELECT Name FROM sysobjects WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1

OPEN procCursor
FETCH NEXT FROM procCursor INTO @name

WHILE  @@FETCH_STATUS = 0
BEGIN  
    EXEC ('grant execute on [' + @name + ']  to TheUserName')

    FETCH NEXT FROM procCursor INTO @name
END

CLOSE procCursor
DEALLOCATE procCursor
GO

This can be used inside a stored procedure whith a parameter for the username for flexibility.

more ▼

answered Dec 08, 2009 at 06:19 PM

dillinzser gravatar image

dillinzser
91 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x415
x89

asked: Dec 08, 2009 at 01:29 PM

Seen: 52731 times

Last Updated: Dec 08, 2009 at 01:29 PM