Sql-server – Grant select to dbo view without select on non dbo tables

schemaSecuritysql serverview

SQL Server 2008 R2

We have a login & user called "JoeBlogs", this user has it's own default schema of JoeBlogs – it has Public access only to the database.

We then granted it SELECT on a dbo view – the dbo view however selects data from underlying tables that belong to a non-dbo schema – so we get an error selecting from the view saying access was denied to underlying tables.

How does one configure the user to have select access to the dbo view but not the underlying non-dbo tables?

Is this even possible?

Jordon

Best Answer

You have to understand the problem before proceeding with the solution.
When you select from a view the SQL Server system would check permissions twice:
Once when you select from the view, and once when the view makes reference to the underlying table.
Obviously the second check would fail if the user has no permission on the underlying table.

Microsoft have solved this with "Ownership Chaining" (known as OC).
OC will bypass the permission check that would be done when the view is referencing the table,
Only if the owner of the view is the same as the owner of the table.
The thing is, OC is bypassing permission checks completely, which means that it can bypass denies as well...
For example, if "JoeBlogs" has the create view permission, he can create a new view which has access to the entire table.
I suggest you read about ownership chaining before you decide your course of action.

books online
msdn blog
detailed example, using a stored procedure instead of view

Having said that, the first Intuitive solution would be to create the view under the same non-dbo schema as the underlying tables (or a schema owned by the same user as the non-dbo schema).
However, if you find that solution to be too risky there is another (and maybe even better) option:
You can always use a function (Multistatement Table-valued Function) with an EXECUTE AS clause:
Create function syntax
execute as clause
This method will allow you to select from the function (execute permission on the function) while the function belongs to dbo. The user specified in the execute as clause must have permissions on the underlying tables.

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user. So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT 
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc] 
,dp.default_schema_name as [database_principal_default_schema_name]
FROM 
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE 
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'

Sql-server – SELECT permission on view querying table from another database

Sounds to me like you have a case of ownership chaining. The link should provide you with the details on how to make sure the chain stays intact.

Best Answer

Related Solutions

Sql-server – The SELECT permission was denied on the (View) object after explicit grant select on view to user

Sql-server – SELECT permission on view querying table from another database

Related Question