Subscribe To This Site

Grant Execute to all Objects in a Database

As the DBA at your firm you don’t want your SQL Server users having direct permissions to modify data directly in your database tables so after a meeting with your development crew, you decide that the application they are developing will only use stored procedures to access and modify data in the database...hooray! I hear you cry.

So this tip will talk you through creating a role that will have execute permissions on all store procedures in a database. A word of warning here, using this method, any member of the role created will have execute permission on all stored procedures in the database regardless of when the stored procedures were created. If you don’t want this, then this tip is not for you...It is possible to modify this process to grant access to objects that belong to a specific schema, thus making your security model more granular, which could very possibly be a more appropriate use of this method.

If you want a user in your database to have EXECUTE permissions on all stored procedures then this tip can assist you.

Solution

Create a role that will be granted execute permission on all objects in your database. This script will create this role for you:--CREATE a Role for that will be given execute permissions in the database

USE [AdventureWorks]
GO
CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GO

The next step is to grant the newly created role execute permissions on the database in question:

--GRANT Execute Permission on the role

GRANT EXECUTE TO [db_executor]
GO

Next we need a login and user that will be added to the db_executor role in this database to get its permissions

So we’ll create a login on the server:

USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD=N'Pa$$w0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Then we’ll create a user for that login in the AdventureWorks database

USE [AdventureWorks]
GO
CREATE USER [Test] FOR LOGIN [Test]
GO

The next task is to add the user to the db_executor role:

USE [AdventureWorks]
GO
EXEC sp_addrolemember N'db_executor', N'Test'
GO

Summary

Grant Execute to all Objects in a Database - Following the steps above I will end up with a database role called “DB_Executor” with execute permisons on all objects in the database. I will also have a login called test mapped to a user of the same name in the adventure works database. The user “test” in the adventure works database has been added to the database role DB_Executor and hence has execute permsions on all objects in the database. As I stated above this method is not for everyone, if you don’t want to manage execute permissions in such an all or nothing way then this is not for you. It is possible to make this more granular by granting execute permissions to all objects that belong to a specific schema.

footer for GRANT Execute page