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


If we can  help you with a SQL Sever problem visit our SQL Server Consulting page or contact us

4 comments:

  1. Right on. I'll be putting this to good use.

    ReplyDelete
  2. First of all I would like to say fantastic blog! I had a quick question that
    I'd like to ask if you don't mind. I was interested to know how
    you center yourself and clear your head prior to writing.
    I've had difficulty clearing my mind in getting my ideas out there.
    I truly do take pleasure in writing but it just seems like the first
    10 to 15 minutes are lost simply just trying to
    figure out how to begin. Any recommendations or hints?
    Thanks!

    ReplyDelete

Featured post

Creating a Linked Server to an Azure SQL Database

Why would you create a Linked Server to an Azure SQL Database? If you work in a hybrid environment with some databases stored on your on ...