Creating a Role for Proc Execution

We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures.

Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy.

So let’s fix that.

With read / write roles assigned to a database we’ll have an exception thrown up if we try to execute a procedure:

The EXECUTE permission was denied on the object ‘TestProc’, database ‘blog’, schema ‘dbo’.

Firstly we’ll create a role which we can assign users to:

CREATE ROLE [db_execute]
AUTHORIZATION [dbo];

Now we’re able to grant the permission to execute procedures to that role:

GRANT EXECUTE TO [db_execute];

With the permission applied we can see this using the system stored procedure:

sp_helprotect

Finally with the role created and permissions granted, we can now add our user to the role:

ALTER ROLE [db_execute]
ADD MEMBER [ExecutePermissionLogin];

That’s all there is to it, happy EXECUTEing!

If you use procedures for the majority of data access then it may be beneficial to add the role to the model database so that any new databases will contain it automatically.

I’m not sure why this isn’t available out of the box. It feels like a very useful role to expose. Well, now we can.