Execute Roles

Question

Post reply

Execute Roles

sxg6023

SSCrazy

Points: 2918
More actions
September 9, 2003 at 9:10 am

#160015

Hi:
I am looking into implementing role based security integrated with windows 2000 AD. All our applications use stored procedures only for
data access & manipulation. I would like use the system defined roles to grant execute permissions to users.
I know that SQL Server has datareader and datawriter roles. But does it have a role that has execute permissions to all
stored procedures so I can just add users to this role?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Unfortunately, nothing like this is built in. You can, however, build it yourself fairly easily:

http://www.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/

K. Brian Kelley
@kbriankelley

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 2

Create your own database role, grant execution permission on sps to this role and add your user to the role.

sxg6023 SSCrazy Points: 2918 More actions · Answer 3

Brian & Allen:

Thanks for the information. I am doing something on the same lines. I just want to make sure this is the right way to do it. May be they add an exec role in the next version.

thanks.

rstone SSCertifiable Points: 6211 More actions · Answer 4

We use such a "execute" role also. We also use only role based security so that we can move objects into production with object permissions (no login worries). This takes care of updating the execute permission in prod. The developers have been pretty good about setting the permission in the source db. The move will fail if somebody is not following standards by adding "special" permissions to their objects or not using dbo as owner.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉