How does SQL Compare work out dependency order?

ChrisGodfree

Facing a strange problem when deploying database changes that we cannot figure out. We successfully deployed a number of database changes to production earlier this week. Among the change were two views - call them A and B with B being referenced in A. When we try to deploy the changes to our demonstration database from the production database (same SQL Compare Command Line options) it fails with a message saying invalid reference to View B. Looking at the SQL scripts which have been generated, the demonstration release script is simply in alphabetical order but the production release script takes into account the dependencies between the views. Does anyone know what could have caused this? The only difference we can identify in the two processes is that the account we use for releasing to the production database has the db_owner role on both databases but the account we use for releasing to the demonstration database has db_reader on the production database and db_owner on the demonstration database.

James B

Thanks for your post.

The internal way that SQL Compare works out dependencies is not something I'm aware of in any detail (it's pretty complex!)

In general it gets things in the correct order; but occasionally certain databases will cause trouble, especially in situations with circular dependency references and so on.

I'd be interested in a couple of tests though- firstly; can you test it using full permissions on both databases as described here? It may be that the reduced permissions on one of the databases are having an effect.

If that makes no difference, does the GUI behave any different to the command line? There's a couple of issues under investigation where the commandline sometimes yields slightly different results, and you may be encountering this.

If neither of the above help it may well be a problem specific to your DB's - if you're able to send snapshots (create these on the File menu) to us, referencing F0064853 in the subject line, we can see if there's anything obvious.

ChrisGodfree

Thanks James, looks like it is permissions.

The GUI behaves the same as the CL but if I'm using a Source database where I only have db_datareader and View Definition on schemas then I find the problem. I did notice that if I right-click on an object and attempt to View Dependencies in the Source database then no results are returned - i.e. no error message. A quick Google search comes back with this which makes sense:

Requires VIEW DEFINITION permission on the database and SELECT permission on sys.sql_expression_dependencies for the database. By default, SELECT permission is granted only to members of the db_owner fixed database role.

We can work around the issue so no problem, just slightly frustrating!

James B

That looks like it - unfortunately SQL Compare does require access to some higher level procedures (more-so if you use encrypted objects at all) to be able to correctly evaluate the database).