Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

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

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.