Grant Execute To All Procedures
Published: Dec 18, 2015
I’ve been in numerous situations now in which people require security in their SQL Server (obviously) but find that the provided db_dataReader and db_dataWriter aren’t quite good enough. They require execute rights on all procedures, but find this is hard to set up.

One way I’ve seen this implemented is to have a proc which goes through every procedure adding the execute rights, but this needs to be on a schedule in order to ensure it catches any new procs that are added to the database. But there is an easier way.

Basically all you need to do in order to allow someone security rights to execute any proc in the database is to simply create a role, add the execute right to that role, and then add the role to the user / user group in question. It’s that simple.

All you need is the following:

use testDB
go

create role db_execute
go

grant execute to db_execute
go

exec sp_addrolemember 'db_execute', 'UserName'
go


In many cases it’s actually easier to do this and then revoke access to specific procs than it is to grant execute to nearly all the procs in your database. It also means you don’t have to have a scheduled job constantly running a loop through all your procs just to add the execute right.

Hope someone finds this useful as I’ve seen this asked a lot online.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron