Re: one SQL DB getting info from another - user setup?
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 06/12/03
- Next message: Dan Guzman: "Re: denying permissions on the view's underlying tables"
- Previous message: John Bell: "Re: Field-Level Security"
- In reply to: Pete Ruby: "one SQL DB getting info from another - user setup?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Wed, 11 Jun 2003 21:01:43 -0500
In order for a user to select from a view without permissions on the
referenced tables, the ownership chain needs to be unbroken. This means
that all of the objects involved need to have the same owner. This is
pretty straightforward when all of the objects are in the same
database - the owners are the same as long as the owner names are the
same.
This is a little tricky with inter-database object references. The
owning users must map to the same login in order for the ownership chain
to be unbroken. If your objects are owned by 'dbo', all 3 databases
need to have the same owner so that the 'dbo' user maps to the same
login. It appears from your narrative that the Main and DB1 databases
have the same owner but DB2 has a different database owner. You can
verify this with sp_helpdb:
EXEC sp_helpdb 'Main'
EXEC sp_helpdb 'DB1'
EXEC sp_helpdb 'DB2'
GO
You can then use sp_changedbowner so that the owner of all 3 databases
is the same:
USE DB2
EXEC sp_changedbowner 'YourCommonDatabaseOwner'
GO
Note that cross-database ownership chaining is a configurable option in
SQL 2000 SP3 and is turned off by default. You can enable it for
selected databases with sp_dboption (like the example below) or via
Enterprise Manager. Alternatively, you can turn it on at the server
level with sp_configure 'Cross DB Ownership Chaining'. In either case,
you should turn on cross-database chaining only if you fully trust
members of the db_owner and db_ddladmin roles.
EXEC sp_dboption 'Main', 'db chaining', true
EXEC sp_dboption 'DB1', 'db chaining', true
EXEC sp_dboption 'DB2', 'db chaining', true
GO
See the SQL Server 2000 SP3 Books Online
<"adminsql.chm::/ad_config_8d7m.htm"> for more information.
-- 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 ----------------------- "Pete Ruby" <pete.ruby@carris.net> wrote in message news:Oulu5hCMDHA.2480@TK2MSFTNGP10.phx.gbl... > I have 3 SQL DB's. The first one, called "Main", has a procedure that pulls > tables from our accounting package into the Main DB. In doing so, it > deletes the old tables, and recreates the new ones. This procedure runs > nightly. One of the tables it updates is called ITEMMAST. > > I have 2 other databases, call them DB1 and DB2. They are both used in > separate web based apps. Both have views that reference Main.ITEMMAST. DB1 > has user assigned to it called (you guessed it) USER1; DB2, USER2. Both > users are SQL type users, listed in the Security\Logins folder in SQL, as > Standard, with Permit access, with DB1, DB2 as their Default database. They > both have no server roles, and both have acess to their own DB, and Main, > with Public Roles. > > In the permissions for Main, USER1 and USER2 are listed, but have NO > permissions set (all option boxes are BLANK) for any of the tables in Main, > including ITEMMAST. DB1 and USER1 run fine w/out having any permissions set > for ITEMMAST. DB2 and USER2 have to have the "SELECT" option for ITEMMAST > selected to be able to run the associated views in DB2. > > The problem is when we run the procedure to update that table, the > permissions are also lost. i realize we can change the procedure to just > delete the records and then append.... > > The real question is: does USER1 and USER2 need specific permision to > ITEMMAST? If so, any ideas why USER1 has no problems running views from DB1 > that reference ITEMMAST? > > Thanx > Pete > > > >
- Next message: Dan Guzman: "Re: denying permissions on the view's underlying tables"
- Previous message: John Bell: "Re: Field-Level Security"
- In reply to: Pete Ruby: "one SQL DB getting info from another - user setup?"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
