December 21, 2005 at 3:51 pm
I created a view and given 'select permission' to a user.
The user runs a select against the view and gets a 'select permission denied on table in database dbtest'. My understanding was that with views you don't have to give table level access. Am I incorrect in that assumption?
CREATE VIEW dbo.agencytest
SELECT
account_id,
language_cd,
last_update_ts
FROM dbtest..table(NOLOCK)
Thanks in advance for any help
Susan
December 21, 2005 at 3:54 pm
If the base tables and view have the same owner, it should work.
December 21, 2005 at 10:03 pm
The problem is they have different owners, as robinhc is alluding to. If the view and the table have the same owner, access need only be granted to the view. Ownership chaining takes care of the rest. However, in the snippet you gave, the view is owned by dbo but the table is owned by dbtest. Since these are two different owners, SQL Server will recheck permissions when accessing the table.
K. Brian Kelley
@kbriankelley
December 22, 2005 at 7:09 am
Brian
Who is the owner when you use ".." like the example?
CREATE VIEW dbo.agencytest
SELECT
account_id,
language_cd,
last_update_ts
FROM dbtest..table(NOLOCK)
December 22, 2005 at 7:39 am
You know, I didn't noticed the .. before. That does make a difference. In that case it's going to look for first an object owned by the user, then it'll drop back to dbo. However, if the view in question, agencytest, does not exist in the same database as the table, that's where the problem lies.
As of SQL Server 2000 SP3, a "feature" called cross-database ownership chaining became configurable. Basically, if the object owners (mapping back to the logins) match up across databases you could get the same ownership chaining feature across databases. In the case of dbo, the login is whoever the database owner is.
However, also as of SP3, it is recommended to turn cross-database ownership chaining off due to security concerns. This is likely what is the case with you. As a result, the permissions check does occur when you cross database lines like that.
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply