Solved

Database roles that can execute

Posted on 2010-11-30
10
291 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
10 Comments
 
LVL 142

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:ScottPletcher
ScottPletcher 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:ScottPletcher
ID: 34241642
S/o else might find them before I do, however, since I'm very busy.
0
 
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now