I get an error when I run this query:
SELECT dependencies.referencing_id as object_id, dependencies.referencing_minor_id as column_id, dependencies.referenced_id AS referenced_object_id, RTRIM(referenced_objects.type) AS referenced_type FROM sys.sql_expression_dependencies AS dependencies JOIN sys.objects AS referenced_objects WITH(NOLOCK) ON referenced_objects.object_id = dependencies.referenced_id WHERE dependencies.referencing_id != dependencies.referenced_id
The SELECT permission was denied on the object 'sql_expression_dependencies', database 'mssqlsystemresource', schema 'sys'.
The Login has membership only in the PUBLIC server role and granted VIEW ANY DATABASE and VIEW ANY DEFINITION. I added a User with membership in the PUBLIC database role for the database I am comparing. I assumed the server level permission would suffice, but I was wrong. I did this on a 2008R2 database in addition to a 2014 database.
On the 2014 database I also granted server level permission SELECT ALL USER SECURABLES. The error persisted.
I granted the User SELECT on the database. The error persisted.
I removed the SELECT permission and added the User to the data_reader database role. The query resolved, however no data is returned (running the same query as a sysadmin returns 130+ rows). Now I'm completely baffled.
I granted SELECT to the user on sys.sql_expression_dependencies explicitly with the same results above.
After researching these permissions, the consensus seems to be that granting SELECT to a User on a database is the same as adding the User to the data_reader role. But this is obviously not the case. I want to create a Login/User with the smallest privilege set possible to achieve the comparison.
How do I grant access to the sys.sql_expression_dependencies view in mssqlsystemresource without granting data_reader access on the whole DB? Is there a way to grant SELECT on mssqlsystemresource only?
**Note - I've read a couple of other posts on SE regarding mssqlsystemresource but they don't pertain to this:
- I have no DENY anything assigned to this Login/User
- Membership in the PUBLIC role (server or database) has no bearing since the permissions are identical to those on a "known good" server.
- I'd rather not have the user have blanket SELECT permissions on the entire database.