Monday, 12 April 2010

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