Solved

Database roles that can execute

Posted on 2010-11-30
10
299 Views
Last Modified: 2012-05-10
what are the roles (in built) that can execute any procs/functions in the database?

thanks
0
Comment
Question by:anushahanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 34241535
from what I know, there is only 1: db_owner
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 125 total points
ID: 34241637
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34241642
S/o else might find them before I do, however, since I'm very busy.
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 34242299
<<what are the roles (in built) that can execute any procs/functions in the database?>>
What database ?
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 125 total points
ID: 34242324
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34248919
a3/Brandon, thanks for confirming that.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34248923
>> Presumably they will be in a sys table somewhere I can find :-) .

you mean Microsoft keeps a list for our reference sake?
0
 
LVL 6

Author Comment

by:anushahanna
ID: 34248930
>>What database ?

either system or user- any DB. would it differ?
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 125 total points
ID: 34260736
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
 
LVL 6

Author Comment

by:anushahanna
ID: 34263787
that is excellent, Ryan. thanks.
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
T-SQL to copy a database 37 66
Convert VBA UDF to SQl SERVER UDF 4 50
SQL- GROUP BY 4 25
SQL Server remove line breaks and tabbed 2 8
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question