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