Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / October 2005

Tip: Looking for answers? Try searching our database.

Stored Procs and db_owner

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
db55 - 31 Oct 2005 23:15 GMT
I have some users that I need to run stored procedures, but they can't
seem to run them unless they are in the db_owner role of the database.

How do I give them access to run the stored procs without giving them
the complete rights of the db_owner role?

Thanks in advance.
Erland Sommarskog - 31 Oct 2005 23:59 GMT
> I have some users that I need to run stored procedures, but they can't
> seem to run them unless they are in the db_owner role of the database.
>
> How do I give them access to run the stored procs without giving them
> the complete rights of the db_owner role?

GRANT EXECUTE ON proc TO user

Or rather than granting permissions directly to users, it's probably
better than adding them roles, and then grant access to the role instead.

But note that just because you grant them access to run the procedures,
that does not mean that they can run the procedures successfully. They
will get SELECT, INSERT, DELETE and UPDATE permissions to tables referenced
by the stored procedures, if the tables and procedures have the same
owner. However, this so-called ownership chaining does not apply to
other statements such as CREATE/DROP TABLE (temp tables are OK) or
TRUNCATE TABLE.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2010 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.