question

Steve Jones - Editor avatar image
Steve Jones - Editor asked

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?

stored-procedurespermissions
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dillinzser avatar image
dillinzser answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tom Staab avatar image
Tom Staab answered

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'
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
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.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

dillinzser avatar image
dillinzser answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered

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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.