Re: Stored Prcedure Security

From: Mike John (Mike.John_at_knowledgepool.com)
Date: 09/24/04 

Date: Fri, 24 Sep 2004 22:52:13 +0100

There is a section in BOL regarding cross database ownership chains and the
behavour in this area changed with SP3. If I remember rightly you will need
to enable cross database ownership chains and ensure BOTH dbs are owned by
the same login , then you will not need to grant rights explicitly to the
user in the other db.

Sorry to be vague, but it isone of those things you set up once, and when
done tend to forget.

Mike John

"Tom Cuomo" <anonymous@discussions.microsoft.com> wrote in message
news:306a01c4a243$c22aa620$a601280a@phx.gbl...
>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: DB access for web apps
    ... For stored procedures in your database, you will have to give an explicit ... EXECUTE permission to this user. ...
    (microsoft.public.sqlserver.security)
  • Re: Execute stored procedure only
    ... right to execute stored procedure in database called "mydatabase". ... deny on every permission and checked grant on execute. ...
    (microsoft.public.sqlserver.security)
  • RE: Security context of stored procedure
    ... Suppose there are three users in a database TEST_CHAIN, ... simple stored procedure: ... also grant it to test_sp2 ... SELECT permission denied on object 'authors', database 'test_chain', owner ...
    (microsoft.public.sqlserver.security)
  • Re: Quick code to execute an SQL stored procedure.
    ... There are alot of post where people don't google for an answer, ... But you are right I googled for "MFC Stored Procedure In 3 Lines of Code" ... I figured that a stored procedure is like a function call to a database. ... I'd use the handle and then call some kind of execute ...
    (microsoft.public.vc.mfc)
  • 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)