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.