0

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.

1 Answer 1

0

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
2
  • I really appreciate the quick response. Would you have a query to check the ownership of the table and view as I cant seem to get this from "right click properties" menu. Feb 22, 2021 at 0:34
  • Perfect! Thank you! Feb 22, 2021 at 0:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.