db_executor role

The process of creating a db_executor might not be granular enough for some but there is a nice simple way of creating a database role in a SQL Server database (called db_executor)  and granting the db_executor role  execute permissions on all stored procedures, then when you want to give someone execute permissions to all stored procedures you simply add them to the db_executor role. As I have already said this may not be for everyone but I have used several times for various clients.

I will use the Adventure database as example here:

First we will create a role in the database called db_executor:

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

The next step is to grant the db_executor role execute permissions, which can be done as follows:

--GRANT Execute Permission on the role


GRANT EXECUTE TO [db_executor]
GO

We will next create a login and user that can be added to the db_executor role in Adventureworks to pick up the permissions:


Create the login:

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

Create the user for the login

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

Add the user to the database role db_executor:

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