db_owner & multiple databases

From: Vladimri Luzhkov (vluzhkov@gw.tcs.spb.ru)
Date: 02/19/03 

From: vluzhkov@gw.tcs.spb.ru (Vladimri Luzhkov)
Date: 19 Feb 2003 07:44:40 -0800

I'm having problem with ability to grant db_owner access for user
(USER) to only one database (database A).
The main problem is that this user must have public (for example)
access to other databases (database B).
All objects in all databases are owned by dbo.
So if this user creates stored procedure in it's own database (A) and
this stored procedure accesses to object in other database (B) user
gain access to this object in database B without checking permissions.

"Online books" tells in "ownership chains" section:
"If the same user owns a stored procedure and all the views or tables
it references, and if the procedure and objects are all in the same
database, SQL Server checks only the permissions on the procedure."
But there is no explanation (or i coudnt find it) of situation, when
objects aren't in the same database.
How can i brake an ownership chain, when it flows to another database?

4 example:
USER exists in db_owner in database A and in public in database B
We have table B.dbo.Table1 without any permissions (or even with deny
all permission to USER).
USER writes stored procedure
CREATE PROCEDURE A.dbo.prcTest AS
SELECT * FROM B.dbo.Table1
GO
And through this stored procedure by EXEC prcTest USER may read data
from Table1. :(

db_owner access to database A is needed to develop this database, and
access to database B is nedded, because SOME (not all) data in
database B is used by client applications and server-side stored
procedures in database A.
I need to maintain abilities of development database A, but to
restrict USER access to some valuable data in database B.
How can I keep it together?

Relevant Pages

  • Re: public role question
    ... Who is the owner of the database? ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: public role question
    ... I logged on to the database through QA as dantest. ... "Dan Guzman" wrote: ... Windows group and thereby get sysadmin permissions. ... Then I am able to create a stored procedure. ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... The main consideration with cross database chaining is that the security implications aren't obvious. ... On the other hand, if data are not sensitive and direct selects are no big deal, go with select permissions. ... > I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • Re: Select Permission Denied On Object
    ... If I were to implement db chaining, what would be a good generic ... table in another database? ... Permissions on indirectly referenced objects are not needed as long as the ... I have a stored procedure in one database, ...
    (microsoft.public.sqlserver.security)
  • 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)