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:
create role db_execute
grant execute to db_execute
exec sp_addrolemember 'db_execute', 'UserName'
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.