Sql-server – How to GRANT SELECT on hidden resource database mssqlsystemresource

sql serversql server 2014sql-server-2008-r2

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

Error:

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.

Best Answer

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 sys.sql_expression_dependencies, and 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 (VIEW/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;
Related Question