16

I am trying to do a Schema Compare in Visual Studio 2010 on some SQL Server 2008 databases. But I get the following error. What does it mean and how can I ignore?

An error was received from SQL Server while attempting to reverse engineer elements of type Microsoft.Data.Schema.Sql.SchemaModel.ISql100DatabaseEncryptionKey: The user does not have permission to perform this action.

Update: "Write Updates" does actually work. But I can't "Export to editor".

jesperlind
  • 2,092
  • 2
  • 20
  • 22

4 Answers4

10

I ran into the same problem. The only fix I found so far is to run the comparison as a user with the sysadmin Server Role. Even selecting to ignore Database Encryption Keys gives this error which seems broken to me. I filed a bug on connect: https://connect.microsoft.com/VisualStudio/feedback/details/552986/schema-compare-permission-error-for-database-encryption-keys-even-though-the-type-is-set-to-be-ignored

Nate Pinchot
  • 3,288
  • 24
  • 35
  • Thank you for reporting this to Microsoft. It is troublesome when like me is having the database on a web host where I can not change the user roles. – jesperlind Apr 25 '10 at 01:27
  • Microsoft closed it as won't fix, however they did give some details on specific permissions that need to be granted to avoid this error (I haven't had a chance to try it yet) - check out the connect link above for details. I think they should have made it workable with a default setup and a login that has db_owner to the database you are working with but I guess they disagree. – Nate Pinchot Apr 29 '10 at 13:39
  • The user I'm connecting with has the sysadmin server role, and it's still producing the error. – Matt Sep 08 '12 at 22:41
  • @MegaMatt Steve Campbell notes that VS 2010 SP1 resolves the issue. Give that a try if you haven't already. – Nate Pinchot Sep 10 '12 at 15:16
  • I'm still encountering the issue on VS 2010 SP1, and the Connect link now appears to be broken. Here's the archive: http://web.archive.org/web/20110819201500/https://connect.microsoft.com/VisualStudio/feedback/details/552986/schema-compare-permission-error-for-database-encryption-keys-even-though-the-type-is-set-to-be-ignored – Caleb Bell Mar 22 '13 at 17:31
2

I found a work around that works for me.

http://www.richardmaxwell.name/blog/2011/3/2/work-around-isql100databaseencryptionkey-blocking-schema-com.html

Richard
  • 236
  • 2
  • 3
2

VS2010 sp1 fixed this issue for me.

Steve Campbell
  • 3,385
  • 1
  • 31
  • 43
0

As mentioned "Write Updates" works and after you update target database you will be able to generate script using "Export To Editor".

samfromlv
  • 1,001
  • 1
  • 10
  • 17