Permission Denied executing SP that reads foreign table!

From: Steve Le Monnier (steve_lemon_at_hotmail.com.nospam.com)
Date: 10/31/05 

Date: Mon, 31 Oct 2005 12:06:40 -0000

 I thought I had a reasonable understanding of how Stored Procedure security
worked, but I've been caught out by something and now I'm a little puzzled.

When a stored procedure is executed the user calling the SP may not have
access to the base tables, but that doesn't matter as the SP runs under the
creators rights and in this case its the dbo who owns the SP. So far so
good.

However we have split our tables across two database (still on the same
server however). We have company data DB's where all requests originate and
to save time and duplication we have a central database holding information
that is common to all company DB's.

When an SP is executed on a company DB that needs to select data from the
central DB, we get a permission denied message.

Why is this? All objects across these databases on the same server are owned
by the same person... the DBO. so why doesn't this work.

In order to resolve this it seems I'm going to have to give select
permissions on certain tables within this central DB and that is what I was
trying to avoid.

Any help on the mechanics of this would be gratefully received

Steve Le Monnier

Relevant Pages

  • Re: Extended Stored Procedure: Get the current db of the client
    ... with the caveat that you don't recommend it because Microsoft ... of a stored procedure versus umpteen of the same stored procedure spread ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ...
    (microsoft.public.sqlserver.odbc)
  • Trying to set up a SQL Server Agent Account and I hit a wall....
    ... Our database is on SQL Server 2005, however it is set to compatibility mode ... You may need to set the compatibility level ... of the current database to a higher value to enable this feature. ... for the stored procedure sp_dbcmptlevel. ...
    (microsoft.public.sqlserver.security)
  • Re: Extended Stored Procedure: Get the current db of the client
    ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ... Did you ever write an extended stored procedure? ... different than an extended stored procedure, so that is not giving you want ...
    (microsoft.public.sqlserver.odbc)
  • Re: Extended Stored Procedure: Get the current db of the client
    ... Thanks for the link Sue. ... I am not going after Gert Sue. ... the database context as a parameter if you need it, ... Did you ever write an extended stored procedure? ...
    (microsoft.public.sqlserver.odbc)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... from any database without having to qualify the stored procedure name fully ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)