Have Geek Brain

Comparison of schema with MS Data Tools in VS2008

When performing a schema comparison with db_owner rights on the target database, the following error occurs:

The user does not have permission to perform this action.

Using SQL Server Profiler, I realized that this error occurs when executing a query targeting the master db: [sys] view. [dm_database_encryption_keys]

With some ignorance of all object types, But Tables, it can be assumed that SQL Compare does not need access to db encryption keys. Also note:

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

One solution would be GRANT VIEW SERVER STATE for the db user, but in my case I am not hosting the database services and am not getting the rights to the server state.

Also tried to exclude DatabaseEncryptionKey element in compare file.

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

Anyone have a workaround?

We are using MS Data Tools versus publishing a database project in DTAP environment.

+2


a source to share


3 answers


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



The error still occurs in this situation, but the button is Write Updates

no longer disabled and the schema is updated as expected.

+1


a source


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



0


a source


Should MS Data Tools be used in VS 2008? I think you should try external tools like:

SQLDBDiff

These are very nice tools for comparing your database schema. also you can generate a script to sync the schema.

0


a source






More articles:


All Articles