View and db permission

[NeotheOne]

Hi all,
I've a view on database db1 created to access data contained on a table on database db2. The owner of the view has permission to create it and to access db2. So everything works until I try to access my view with another db1 user with no permission on db2.

This is normal ? Is there any db/server option/parameter I could set to solve this issue or should I grant access to db2 to each db1 users ?

Thanks

Achille

 

[JamesLean]

Have a look at this thread:

Setting permissions for SP with cross-database join
thread962-1035694

--James

 

[NeotheOne]

Hi,
A looked at thread, and some others concerning the same issue, but there is something I can't understand.

I've 2 db, db1 and db2.
With user user1, db_owner on both db, I create table2 on db2 and then a view1 on db1 to access table2 on db2.

Then I create user2 with no access to db2, and I enable db chaining on both db. But if I try to query view1 with user2
, this error is returned

Server user 'user2' is not a valid user in database 'db2'

What's wrong ?

Thanks

 

[mrdenny]

You'll need to create that user in the db2 database.

Cross-database permissions are not allowed; permissions can be granted only to users in the current database for objects and statements in the current database. If a user needs permissions to objects in another database, create the user account in the other database, or grant the user account access to the other database, as well as the current database.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NeotheOne]

Hi Denny,
This is really the only solution ?
So every user who needs to access data by a view to another database needs also to be enabled to the remote db ?!?!?
I think in this case a view lost much of its usefulness...

Probably I'ven't really understood db chaining settings. Which is the difference between setting it ON or OFF ?

Thanks
Bye

 

[mrdenny]

I'm not to up on the database chaining at all. I've found very little about it in the docs.

Come to think of it, I haven't seen anything about it in SQL 2005 so it may be going away.

In order to query the object, you have to have rights to the object. In this case I think the best bet would be to create a domain group or a local group, and put all the users in that group. Then give that group rights in both databases. Then you simple have to add the users to the group.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)