Stored Procedure Security/Permissions

From: Tom C (anonymous_at_discussions.microsoft.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 10:47:54 -0700

I have a stored procedure that selects from tables in
multiple databases on the same server. The stored
procedure is cataloged in one of the databases.

For example: The stored procedure TESTA is cataloged in
cede_70 by cede_70_dbo who is the dbo of both databases

create procedure dbo.TESTA
as

select *
from cede_70.dbo.tableA
   , cede_rpt.dbo.tableB
where cede_70.dbo.tableA.columnone =
cede_rpt.dbo.tableB.columnone

go
grant execute on TESTA to public
go

Under SQL server 2000 SP3a when someone executes the
stored procedure TESTA it fails with a message like
"SELECT permission denied on object 'TableB',
database 'cede_rpt', owner 'dbo'. "

The login executing the stored procedure does not have
permission to read either table; but the login does have
permission to execute the stored procedure, which was
cataloged by the dbo (who has permission to read both
tables). It is the read of the table from the 'other'
database (the one without the stored procedure) which
fails.

Should I really have to add the User to the
cede_rpt.dbo.tableB database too! I thought the mere fact
that it has EXECUTE permission to the SP would suffice!

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: Stored Procedure Security/Permissions
    ... Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 ... > permission to read either table; ... > permission to execute the stored procedure, ...
    (microsoft.public.sqlserver.security)
  • Re: Profile Trace: how to tell which database when filtering on user
    ... and Warnings event class. ... The databases need to be owned by the same login. ... it was because the user lacked execute privileges on the procedure. ... it's executing the stored procedure in DB#1. ...
    (microsoft.public.sqlserver.security)