Schema compare with MS Data Tools in VS2008

Question

When performing a schema compare having db_owner rights on the target database results in the following error:

The user does not have permission to perform this action.

Using the SQL Server Profiler I figured out this error occurs executing a query targeting the master db view: [sys].[dm_database_encryption_keys]

While specifically ignoring all object types but Tables one would presume the SQL Compare doesn't need access to the db encryption keys. Also note:

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/c11a5f8a-b9cc-454f-ba77-e1c69141d64b/

One solution would be to GRANT VIEW SERVER STATE to the db user, but in my case I'm not hosting the database services and won't get the rights to the server state.

Also tried excluding DatabaseEncryptionKey element in the compare file.

 <PropertyElementName>
    <Name>Microsoft.Data.Schema.Sql.SchemaModel.SqlServer.ISql100DatabaseEncryptionKey</Name>
    <Value>ExcludedType</Value>
 </PropertyElementName>

Anyone has an workaround this?

We're using MS Data Tools compare to publish from a Database project into a DTAP environment.

Answer 1

Why can't you try other (external) schema comparison tools? Check out Red gate's SQL Compare.

Answer 2

Hi,

Is it must be using MS Data tools in VS 2008? I think you should try an external tools like this:

SQLDBDiff

This very nice tools for comparing schema of your DB. also you can generate script to sync the schema.

Answer 3

The only successful workaround I've found is to deploy the schema to a local development database and then do the schema compare using that local database as the source instead of the database project.

The error still comes up in this situation, but the Write Updates button is no longer disabled and the schema updates as expected.