September 9, 2003 at 9:10 am
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?
September 9, 2003 at 9:12 am
Unfortunately, nothing like this is built in. You can, however, build it yourself fairly easily:
http://qa.sqlservercentral.com/columnists/bkelley/sqlserversecuritythedb_executorrole.asp
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
September 9, 2003 at 9:14 am
Create your own database role, grant execution permission on sps to this role and add your user to the role.
September 10, 2003 at 8:12 am
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.
September 10, 2003 at 9:49 am
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.
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply