Azure SQL – Select Permission Denied on Object

azure-sql-databaseSecurity

As an example, I have a database called 'Database1' on Azure SQL Server 12.0.2000.8
I needed to setup an account that could only access a view in the database (eg. View1).
This was achieved by the following:

CREATE USER [testuser] WITH PASSWORD 'P@ssword1!'
GO
GRANT SELECT ON [dbo].[View1] to [testuser]
GO

This worked perfectly. I was able to connect to the Database and only see the view and not the related tables.

My colleagues then utilized this method to create additional user accounts with read access to other views.

Recently the user accounts have been unable to access the views with the following error msg.

"Select permission denied on object 'table1', database 'Database1', schema 'test'"

I have checked that there is no deny permission against the accounts.
If I grant select on the specified table (table1), the issue is resolved, however this enables the user account to view the table contents which I would like to avoid.

Any assistance in troubleshooting this would be appreciated.

Best Answer

SELECT permission on a view doesn't grant SELECT permission on the referenced tables. Instead if the view and the tables have the same owner, there is an "ownership chain" between them and permissions on the tables are not checked.

So ensure that the view and tables have the same owner.

Objects are owned by the owner of their schema by default. It is possible (but unwise) to change the ownership of a single object. Anyway you can list the objects and their owners like this:

select o.name, type_desc, user_name(coalesce(o.principal_id,s.principal_id)) owner
from sys.objects o
join sys.schemas s
  on o.schema_id = s.schema_id
where o.is_ms_shipped = 0
  and type_desc in ('USER_TABLE','VIEW')
order by type_desc, name
Related Question