A view on two databases.

Question

A view on two databases.

Roust_m

SSCoach

Points: 17410
More actions
January 29, 2004 at 9:07 am

#358934
Hi!
Made a view, that takes data from tables table1 & table2 from databases db1 & db2. The view itself is in db1. User Smith has "select" right on the view. For "select" statement he gets:
Server: Msg 229, Level 14, State 5, Line 1 SELECT permission denied on object 'table2', database 'db2', owner 'dbo'.
Both the view and 'table2' have the same owner, - dbo. Option 'db chaining' is turned on on both databases. Nopermissions on 'table2', of course, cause user should not see the table. User has login in both databases. No guest accounts in both databases, cause I rely on database ownership chaining. (This all about MS SQL 2000 sp3)
What is wrong?
Thanks.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

I'll see if I can find the exact info in BOL, but the issue is that the user still needs permissions to the table.

The view just lets the user see the information that you want them to see, if you haven't given them access to the information how can they see it.

One way to look at is that a view is a Virtual Table, it really doesn't have any information in it. The view/virtual table gets selected information when a user requests it from the view/virtual table. (I know it's not REALLY a virtual table, but the description works for my explaination - I hope).

Again, I'll see if I can find where the information is in BOL.

-SQLBill

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Found it!

In the Books OnLine, use the Index tab and enter Create View, scroll down to permissions.

According to the BOL, a View IS a Virtual Table. To EXECUTE a view, the user must have SELECT permission on the table being referenced in the view.

-SQLBill

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 3

Can't reproduce your problem. Once "allow cross-database ownership chaining" is enabled on both databases and "select" permission be granted to the user, The user can run "select" on the view without problem.

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Allen,

The poster said the user DOESN'T have permissions on Table 2.

"Nopermissions on 'table2', of course, cause user should not see the table"

And that's the problem. SELECT has to be granted to the user on Table 2.

-SQLBill

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 5

Yes, The user has no permission to select on both tables. And it works as user be granted to select on view and 'DB cross-ownership chaining' is on.