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.
Not sure where you’ve stumbled along the way, but this works for me:
CREATE LOGIN permtest WITH PASSWORD = 'x', CHECK_POLICY = OFF; GO USE somedatabase; GO CREATE USER permtest FROM LOGIN permtest; GO
According to this page, the user needs
SELECT permission on
VIEW DEFINITION on the database.
In my experimentation, the following allowed the user to select from the view, but it returned 0 rows, because they don’t have the ability to view definition (which includes dependency chains):
GRANT SELECT ON sys.sql_expression_dependencies TO permtest;
In order to actually see any relationships in
somedatabase, I also had to add the following:
GRANT VIEW DEFINITION ON DATABASE::floob TO permtest;
I could not find any way to make that more granular (
DENY definition worked for individual objects, but without the database-level right, I still couldn’t see any rows in the catalog view, and
DENY did not prevent the objects from showing up in the catalog view nor did it even prevent me from viewing the definition). I feel like SQL Server would have a hard time resolving that granularity anyway – if you had a view that referenced a table, how should the catalog view look if you have grant on the view and deny on the table, or vice versa?
If you don’t want to grant
VIEW DEFINITION on the database, then create procedures that use
EXECUTE AS OWNER, select (filtered?) rows from the catalog view, and give the users (and of course, that could also be a role) execute permissions on the procedure.
CREATE PROCEDURE dbo.GetDependencies WITH EXECUTE AS OWNER AS BEGIN SELECT is_schema_bound_reference --, ... FROM sys.sql_expression_dependencies; END GO GRANT EXECUTE ON dbo.GetDependencies TO permtest;