CODEWITHSUNDEEP
sql-server
sniegoman
sniegoman

Reputation: 59

Grant SELECT permission on a view in reporting schema, but not on underlying objects in different schemas

I am preparing set of views which users can use to prepare their own reports. The views query tables from other schemas including dbo. The views are created in a separate REPORTING schema. I have configured following user which will be used to access the data by end users:

CREATE ROLE report AUTHORIZATION db_securityadmin;
GRANT SELECT, EXECUTE ON SCHEMA :: reporting TO report 
CREATE LOGIN reporting_login WITH PASSWORD = 'SomePassword'
CREATE USER reporting_usr FOR LOGIN reporting_login
EXEC sp_addrolemember N'report', reporting_usr'

When tried to query my view I got an error saying that: 'The SELECT permission was denied on the object XXX schema dbo'. So following this topic: Grant Select on a view not base table when base table is in a different database (clarification: I have all of my tables and views in the same database) I changed the ownership of the views to dbo user(ALTER AUTHORIZATION ON reporting.[vCounters] TO dbo) , which worked well for views that only used dbo tables, but when I started using other schemas I started getting permission denied errors again on those tables.

All schemas used by views are owned by same ApplicationAdmin user, dbo schema is obviously owned by dbo. How can I apply ownership chain properly to allow reporting_usr to query from views in reporting schema but not underlying tables in other schemas.

Upvotes: 0

Views: 977

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89406

How can I apply ownership chain properly to allow reporting_usr to query from views in reporting schema but not underlying tables in other schemas.

The views and tables must have the same owner. If your reporting users have no rights to create objects in the reporting schema, then just change its owner to dbo.

Upvotes: 0

Related Questions