Re: Impossible to use a view of a table from another database
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 25 Sep 2007 21:18:00 +0000 (UTC)
Mike (Mike@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I have two database on the same server. Database A and datadase B. I am
using sql server for the databases and Microsoft Access as my front
end.
I create a view in database A to see a table in database B.
no problem here.
I run the view in the query analyser (Database A)
no problem here
My problem start when I try to run the same query as a pass-trought query
inside Microsoft access.
If I run the pass-trought query as being the database owner of both
database it work.
But as soon as I try to use the same pass-trought query as another user (A
user with select, write, update, and delete permission on the view in
database A or even with permission on the table in database B) I get the
messge in Microsoft Access (Invalide object name). Even if I have the
permission to select the view, Microsoft Access is not able to see or make
the link to that view.
I don't have any problem with any other views, only the views that refer
to table in another database on the same server.
Do some one know why SQL server don't let My front end see that view, even
if I gived the permission for that user to select that view.
Since you mention Query Analyzer, I assume in SQL 2000.
The mechanism at hand here is ownership chaining. With ownership
chaining, you can grant permission on a view without granting
access on the underlying tables, as long as the view and table
owners are the same.
Up to SQL 2000 SP2 this also work across databases. That is, if both
databases had the same owner, and the same login mapped to the same
database user. A simple case: the databases were both owned by sa, and
all objects were owned by dbo.
In SQL 2000 SP3, Microsoft changed this, so that by default database
chaining is disabled, because there are security issues with it on a
server where the DBA does not have control over all databases.
To enabled DB chainging, you need to do:
sp_configure 'Cross DB Ownership Chaining', 1
RECONFIGURE
go
exec sp_dboption firstdb, 'db chaining', true
exec sp_dboption seconddb, 'db chaining', true
But do not do this, unless you have full control over the server.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Prev by Date: RE: Permissions required to select from sys.services?
- Next by Date: Re: SQL Domain Group Permissions
- Previous by thread: RE: Permissions required to select from sys.services?
- Next by thread: Re: SQL Domain Group Permissions
- Index(es):
Relevant Pages
|
