Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pjstreiff
Starting Member
12 Posts |
 Posted - 2009-03-16 : 14:11:01
|
| I have been creating custom db role named db_procexecuter, granting exec on all sp's to it and adding users to the role which need permission to execute stored procedures.Then I found out about 'GRANT EXECUTE ON DATABASE::[user_db] to [user_acct]'2nd way seems lower maintenance because does not require me to regrant exec rights everytime a new sp is created. Drawback seems to be that I can't tell who/what has exec permission easily.What are the opinions out there on the use of these 2 approaches?Thanks,-pjstreiff-pjstreiff |
|
|
guptam
Posting Yak Master
161 Posts |
Posted - 2009-03-16 : 14:54:49
|
| Hi Pjstreiff, Second way is much easier and better way to do it then having to grant execute on each object. However I would still use database roles to grant permissions and never grant execute on database level instead use schema level.1) Create new database roll called udbr_SPPermissions.2) Execute GRANT EXECUTE ON SCHEMA::[SCHEMANAME] TO [DBRoll].3) Add users to the database role.Reason I choose that approach is, it is easier to group people by db role. And if you are working on a larger system you can group permissions by functional schema instead of entire database.My two cents...Thanks.-- Mohit K. GuptaB.Sc. CS, Minor JapaneseMCITP: Database AdministratorMCTS: SQL Server 2005http://sqllearnings.blogspot.com/ |
 |
|
|
|
|
|
OPINIONS PLS: Grant exec db vs sp??