Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.