Re: stored procedures and permissions

From: Sue Hoegemeier (Sue_H_at_nomail.please)
Date: 06/30/04 

Date: Tue, 29 Jun 2004 19:18:50 -0600

The first line is not exactly correct. Understanding
ownership chains and how those work is the part that's
missing. All the objects need to be owned by the same user.
In that case, SQL Server checks permissions on the source
object only. For example, if the tables and stored
procedures have the same owner, you can grant execute on a
stored procedure to a user and the user does not need
permissions to the underlying tables.
If you have different owners for objects in the chain, SQL
Server checks permissions on each object where the owner is
different. If the user hasn't been granted permissions on
the underlying objects with different owners, you get the
permission denied error.
You can find some good information on ownership chains and
how they work in books online.

-Sue

On Tue, 29 Jun 2004 11:59:35 -0700, Damon Torgerson
<damon@ranksix.com> wrote:

>It is my understanding that when a user executes a stored procedure they
>are executing the stored procedure with the permissions of the stored
>procedure owner.
>
>However I am running into a problem that I cannot explain and would love
>it if someone might be able to shed some light on my problem.
>
>I have three users: myproject, myproject_app, and myproject_web
>
>- myproject owns all tables and views
>
>- myproject_app owns all stored procedures
>
>- myproject_web can execute some stored procedures
>
>When myproject_web tries to execute a stored procedure I receive errors
>that are similar to:
>
>Server: Msg 229, Level 14, State 5, Procedure getAllEmployees, Line 3
>SELECT permission denied on object 'employees', database
>'some_database', owner 'myproject'.
>
>Below is an example that produces the error...
>
>/* create a table */
>
>create table myproject.employees (
> id int,
> first_name varchar(100)
>)
>go
>
>/* grant select on table to user myproject_app */
>
>grant select on myproject.employees to myproject_app
>go
>
>/* insert some data */
>
>insert into myproject.employees (id, first_name) values (1, 'Fred')
>go
>insert into myproject.employees (id, first_name) values (2, 'Sally')
>go
>insert into myproject.employees (id, first_name) values (3, 'Dwayne')
>go
>
>/* create stored procedure */
>
>create stored procedure myproject_app.getAllEmployees
>as
>select id,
> first_name
>from myproject.employees
>go
>
>/* grant execute to user myproject_web */
>
>grant execute on myproject_app.getAllEmployees to myproject_web
>go
>
>/* as user myproject_web execute the stored procedure */
>
>exec myproject_app.getAllEmployees
>
>/* returns the following error
>
>Server: Msg 229, Level 14, State 5, Procedure getAllEmployees, Line 3
>SELECT permission denied on object 'employees', database
>'some_database', owner 'myproject'.
>
>*/

Relevant Pages

  • Re: stored procedures and permissions
    ... Suppose I have two developers one with high security clearance ... > permissions to the underlying tables. ... > Server checks permissions on each object where the owner is ... >>It is my understanding that when a user executes a stored procedure they ...
    (microsoft.public.sqlserver.security)
  • Re: SELECT permission denied on object
    ... In order to avoid granting direct permissions to the referenced objects, ... I have a stored procedure created with another account ... > database 'database_name', owner 'dbo'. ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE Permissions and Cross Database
    ... (even though the owner was already sa). ... Make sure that the user on C database IS an owner of 'dbo' SCHEMA as well ... I have a user that has EXECUTE and SELECT permissions on each ... The stored procedure works fine from database A, but from database B, ...
    (microsoft.public.sqlserver.security)
  • Re: Rights to change an SP
    ... When you have other objects referenced in a stored procedure ... permissions on the objects will different owners. ... granting execute permissions on such a stored procedure ... user other than the owner of the stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Object permissions
    ... Who is the owner of the query? ... permissions does the owner have on the underlying tables? ... does the user have on the query; ... to 'owners' in the sql statement each time the code runs, ...
    (microsoft.public.access.security)