SQL Server 2005: Stored Procedures Executor Role

Granting permissions to use stored procedures is something I rarely do… in most of my applications, I either use .NET SQL projects and compile my SQL code OR everything is handled in the data layer/LINQ code.

We’ve had a recent consult project and the coders use stored procedures heavily.  That’s cool—to each their own.  Performance seems good on the system and the procedures are pretty small.

But… remembering to readd the EXECUTE permission to the user account on each reload is REALLY annoying.  I’d read this somewhere (if I can find the original source, I’ll cite it) ages ago and dragged it out of the code library.  A quick bit of TSQL code to create a database role that SHOULD exist out of the box—one that can execute stored procedures.

/* Create a new role for executing stored

   procedures */

CREATE ROLE db_executor

 

/* Grant stored procedure execute rights

   to the role */

GRANT EXECUTE TO db_executor

 

/* Add a user to the db_executor role */

EXEC sp_addrolemember ‘db_executor’, ‘AccountName’

Quite easy and it makes it VERY easy to control EXECUTE permissions for your accounts.

Tags: SQL+Server+2005, TSQL, Stored+Procedures, EXECUTE, asp.net