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

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(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

avatar image

dillinzser
91 1 3 5

(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

avatar image

Tom Staab ♦
14.5k 7 14 18

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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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

avatar image

Pavel Pawlowski
22.7k 10 15 26

(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

avatar image

dillinzser
91 1 3 5

(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:

x454
x108

asked: Dec 08, 2009 at 01:29 PM

Seen: 62553 times

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

Copyright 2016 Redgate Software. Privacy Policy