What permissions are needed to view dependencies?

Question

My company is moving to a much more restrictive security model in the near future.

As part of that we are changing specific servers to the new model and testing all our existing processes. One of the processes that I use a lot utilizes the SMO framework to script out database objects in dependency order, so we can run those scripts on another server.

The dependency order piece is very important as we have a lot of nested objects (think views that reference other views).

With the new security model in place, this script stopped working correctly.

I tested in SSMS as well (SSMS 2012 against a 2008r2 instance) and using View Dependencies on a view that references another view in the same DB doesn't show the referenced view under Objects on which [this view] depends.

Even more troubling, if I run sp_depends on the view in question I do get an accurate list of dependencies.

I did a bit of research and couldn't find a definitive answer, so I'm hoping someone can help me out:

What specific permissions are needed for a user to accurately view dependencies in SQL Server 2008r2 and/or SQL Server 2012 (we are upgrading soon).

Answer 1

The 2012 Books Online topic "Get Information About a View" states that the permissions required for this specific task are:

• VIEW DEFINITION permission on the database; and
• SELECT permission on sys.sql_expression_dependencies

Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; object-level VIEW DEFINITION and SELECT on the DMV will not work (you will receive an empty result set).

Example:

USE Sandpit;
GO
CREATE VIEW dbo.V1 AS SELECT 1 AS const;
GO
CREATE VIEW dbo.V2 AS SELECT v.const FROM dbo.V1 AS v;
GO
CREATE USER Bob WITHOUT LOGIN;

-- These two permissions are not sufficent
GRANT VIEW DEFINITION ON OBJECT::V1 TO Bob;
GRANT VIEW DEFINITION ON OBJECT::V2 TO Bob;

-- This one permission is enough (covers the two above)
GRANT VIEW DEFINITION ON DATABASE::Sandpit TO Bob;

-- Also required
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO Bob;
GO
EXECUTE AS USER = 'Bob';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V1', N'V'));
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V2', N'V'));

-- Show DMV info for the test views
SELECT
    referencing_object_name = OBJECT_NAME(sed.referencing_id),
    sed.referencing_class_desc,
    sed.is_schema_bound_reference,
    sed.referenced_class_desc,
    sed.referenced_schema_name,
    sed.referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE
    sed.referenced_id = OBJECT_ID(N'dbo.V1', N'V');
GO
REVERT;
GO
DROP USER Bob;
DROP VIEW dbo.V1, dbo.V2;

Regarding sp_depends: that requires only membership of the public role but is deprecated:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.