locked
View Dependency permissions? RRS feed

  • Question

  • I need users to be able to view dependencies within SSMS on objects in a DB.  I've given them every permission at the database level, except for control, and even some server level permissions, but they still can't see dependencies.  They don't get errors, but the can't see the dependencies in the window.  Surely this can't be right...

    The view dependency ability seems rather buggy to me.  I tried many combinations of permissions, and always got errors of some type...some were even for the mssqlsystemresource DB!

    I've googled the crap out of this, but can't seem to find an answer.  Does anywone know what the best combination of permissions for viewing dependencies is?  Thanks!


    Clint
    Friday, March 18, 2011 9:55 PM

Answers

  • Hi Clint,

    Thanks for your update.

    Based on my tests. granting the users VIEW DEFINITION permission should be enough to view object dependency. However, if VIEW DEFINITION is denied for the user, the user may be not able to view dependency even if grant it later because DENY is overlap GRANT. If that is the case, please firstly remvoe the premission and than grant it again, for example:

    USE [<db_name>];
    GO
    REVOKE VIEW definition FROM <user>;
    GO
    GRANT VIEW definition TO <user>;
    GO
    

    If the issue persists, please let me know SELECT @@VERSION and version information of Management Studio you use.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by NashVegas Friday, March 25, 2011 8:00 PM
    Friday, March 25, 2011 6:32 AM

All replies

  • See if that helps you

    USE master
    GO
    CREATE PROCEDURE sp_FindDependencies
    (
            @ObjectName SYSNAME,
            @ObjectType VARCHAR(5) = NULL
    )
    AS
    BEGIN
        DECLARE @ObjectID AS BIGINT

            SELECT TOP(1) @ObjectID = object_id
            FROM sys.objects
            WHERE name = @ObjectName
            AND type = ISNULL(@ObjectType, type)

        SET NOCOUNT ON ;

          WITH DependentObjectCTE (DependentObjectID, DependentObjectName, ReferencedObjectName, ReferencedObjectID)
            AS
            (
            SELECT DISTINCT
                   sd.object_id,
                   OBJECT_NAME(sd.object_id),
                   ReferencedObject = OBJECT_NAME(sd.referenced_major_id),
                   ReferencedObjectID = sd.referenced_major_id
            FROM   
                   sys.sql_dependencies sd
                   JOIN sys.objects so ON sd.referenced_major_id = so.object_id
            WHERE  
                   sd.referenced_major_id = @ObjectID
            UNION ALL
            SELECT
                   sd.object_id,
                   OBJECT_NAME(sd.object_id),
                   OBJECT_NAME(referenced_major_id),
                   object_id
            FROM   
                   sys.sql_dependencies sd
                JOIN DependentObjectCTE do ON sd.referenced_major_id = do.DependentObjectID      
            WHERE
                   sd.referenced_major_id <> sd.object_id    
            )
            SELECT DISTINCT
                   DependentObjectName
            FROM  
                   DependentObjectCTE c
    END
    ----Usage
    EXECUTE sp_ms_marksystemobject \'sp_FindDependencies\'


    EXECUTE sp_FindDependencies \'SalesHistory\'


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Saturday, March 19, 2011 8:46 AM
  • I've given them every permission at the database level, except for control, and even some server level permissions, but they still can't see dependencies.

    Including VIEW DEFINITION?

    Make everything as simple as possible, but not simpler.
    -Albert Einstein
    Tuesday, March 22, 2011 11:51 PM
  • This helps, and I thank you, but it's not really what I'm looking for.  I want to be able to use the interface provided through SSMS and actually have it work as it should...
    Clint
    Wednesday, March 23, 2011 4:53 PM
  • I've given View Defifnition, along with every other permission, and it doesn't work.  The only thing I can get to work is either CONTROL permission at the DB level, or add user to db_owner role
    Clint
    Wednesday, March 23, 2011 4:55 PM
  • Hi Clint,

    Thanks for your update.

    Based on my tests. granting the users VIEW DEFINITION permission should be enough to view object dependency. However, if VIEW DEFINITION is denied for the user, the user may be not able to view dependency even if grant it later because DENY is overlap GRANT. If that is the case, please firstly remvoe the premission and than grant it again, for example:

    USE [<db_name>];
    GO
    REVOKE VIEW definition FROM <user>;
    GO
    GRANT VIEW definition TO <user>;
    GO
    

    If the issue persists, please let me know SELECT @@VERSION and version information of Management Studio you use.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by NashVegas Friday, March 25, 2011 8:00 PM
    Friday, March 25, 2011 6:32 AM
  • Well, it seems to be working with only the VIEW DEFINITION permission granted.  However, I did not specifically have any permisisons denied, so either some other permission granted interferes with the VIEW DEFINITION permission as it relates to dependencies, or there was some other problem happening that day that was causing it.  Thanks!
    Clint
    Friday, March 25, 2011 8:02 PM