Re: db_owner & multiple databases
From: Dan Guzman (danguzman@nospam-earthlink.net)
Date: 02/19/03
- Next message: Mitch: "remove user"
- Previous message: Vladimri Luzhkov: "db_owner & multiple databases"
- In reply to: Vladimri Luzhkov: "db_owner & multiple databases"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Dan Guzman" <danguzman@nospam-earthlink.net> Date: Wed, 19 Feb 2003 10:08:00 -0600
The database owner login determines the object owner with dbo-owned
objects and thereby the SID used for ownership chain checking.
Cross-database ownership chaining will be allowed only if:
1) the databases have the same owner as reported by sp_helpdb
2) introduced in SQL Server 2000 SP3, you have enabled chaining at the
server level or at the database level for the databases involved
To lock down your development database, you can specify a different
database owner (using sp_changedbowner) and/or disable cross-database
chaining (using sp_dboption 'db chaining'). Cross database chaining
behavior is described in detail in the Books Online distributed with
SP3.
-- Hope this helps. Dan Guzman SQL Server MVP ----------------------- SQL FAQ links (courtesy Neil Pike): http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800 http://www.sqlserverfaq.com http://www.mssqlserver.com/faq ----------------------- "Vladimri Luzhkov" <vluzhkov@gw.tcs.spb.ru> wrote in message news:bea9dbb9.0302190744.6545296b@posting.google.com... > 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?
- Next message: Mitch: "remove user"
- Previous message: Vladimri Luzhkov: "db_owner & multiple databases"
- In reply to: Vladimri Luzhkov: "db_owner & multiple databases"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
|
