http://www.sqlservercentral.com/blogs/sqldbauk/2010/04/12/db_5F00_executor-role/

Printed 2014/10/22 07:01PM

db_executor role

By Gethyn Ellis, 2010/04/12

This process might not be granular enough for some but there is a nice simple way of creating a role in a SQL Server database  and granting it 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 role. As I have already said this may not be for everyone but I have used it a couple of places I have been. 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 role executor 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 this 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


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.