Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

db_executor role

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

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.