I would like to enable a SQL server user with read-only access to a DB the ability to view object dependencies. As a sysadmin I can select a table in SSMS and view any dependent objects (views, SPROCs, etc). My read-only users can see very few or none of these dependencies. I have already granted view definition but that didn't do it.
Does anyone know if there is a SQL server permission for this?
Thanks.
VIEW OBJECT DEFINITION granted on the relevant objects, or VIEW ANY DEFINITION. Note that the permission covers more than just dependencies and allows the grantee to view the entire definition of the object (source of a stored procedure, original query of a view, table definition etc etc.
If you want a more granular restriction you would have to customize it via a stored procedure that returns the dependencies and is code-signed to have the proper permissions, but this way users must use your procedure to view the dependencies (ie. the SSMS and alike tools won't work).