Is there an easy way to grant execute permissions for a user to all stored procedures at once rather than doing each one individually?
asked
Grant execute permission to all stored procedures
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.
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'
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.
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.
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
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.
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
