stored procedures and permissions

From: Damon Torgerson (damon_at_ranksix.com)
Date: 06/29/04 

Date: Tue, 29 Jun 2004 11:59:35 -0700

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: Execute stored procedure only
    ... You need CONNECT capability to be able to get into the database and execute ... You can compare this to having permission to eat a piece of pie, ... Should I not be able to just execute SP through QA? ... right to execute stored procedure in database called "mydatabase". ...
    (microsoft.public.sqlserver.security)
  • Re: EXECUTE permission denied on object... uh?
    ... connecting with doesn't have permission to execute the Stored Procedure ... runBehavior, SqlCommand cmdHandler, SqlDataReader ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Exec permissions on Stored Procedure
    ... they have Select permissions on a table that the Stored Procedure uses? ... execute permission on a stored procedure, that's all that needs to be ... requiring me to also grant select permission to the underlying table. ... does not apply to dynamic SQL, because dynamic SQL is not part of the ...
    (microsoft.public.sqlserver.security)
  • RE: SQL stored procedure executing twice
    ... I wasn't aware that DLookupwould execute the "domain" more than once. ... caused the stored procedure to execute twice. ... Dim stDocName As String ... My pass-thru query properties ...
    (microsoft.public.access.modulesdaovba)
  • Re: using EXECUTE AS for stored procedures, sql 2005
    ... Each stored procedure has one, and exactly one account that is ... EXECUTE AS OWNER. ... execute from the security context of said owner. ...
    (microsoft.public.sqlserver.programming)