Database roles that can execute

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

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
from what I know, there is only 1: db_owner
0
 
Scott PletcherConnect With a Mentor Senior 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<what are the roles (in built) that can execute any procs/functions in the database?>>
What database ?
0
 
BrandonGalderisiConnect With a Mentor Commented:
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 McCauleyConnect With a Mentor Data and Analytics ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.