• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Database roles that can execute

what are the roles (in built) that can execute any procs/functions in the database?

thanks
0
anushahanna
Asked:
anushahanna
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what I know, there is only 1: db_owner
0
 
Scott PletcherSenior DBACommented:
Even default users can use certain system procs in any/every db.

For example, I think sp_help is available to everyone; what it returns depends on your specific object permissions.

I know there are a few others.  I don't know all of them off the top of my head.  Presumably they will be in a sys table somewhere I can find :-) .
0
 
Scott PletcherSenior DBACommented:
S/o else might find them before I do, however, since I'm very busy.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<what are the roles (in built) that can execute any procs/functions in the database?>>
What database ?
0
 
BrandonGalderisiCommented:
As angel has said, for user procedures only DBO has execute rights by default.  You must grant execute to the other user/roles you want to have access.
0
 
anushahannaAuthor Commented:
a3/Brandon, thanks for confirming that.
0
 
anushahannaAuthor Commented:
>> Presumably they will be in a sys table somewhere I can find :-) .

you mean Microsoft keeps a list for our reference sake?
0
 
anushahannaAuthor Commented:
>>What database ?

either system or user- any DB. would it differ?
0
 
Ryan McCauleyCommented:
You can grant a blanket execute right on all the stored procs in a database (instead of each one) by doing this:

GRANT EXECUTE TO YourUser

Open in new window


I've always disliked that there's no built-in database role to do this, like there is db_datareader, db_ddladmin, and so on, so one of the first things I do on our SQL Servers is create a DB_EXECUTOR database role, and then it's available to grant to users that need this right:

CREATE ROLE db_executor
GRANT EXECUTE TO db_executor

exec sp_addrolemember 'db_executor','YourUser'

Open in new window

0
 
anushahannaAuthor Commented:
that is excellent, Ryan. thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now