Can't compare encrypted text in SQL Server 2005 or 2008
Published 04 June 2013
Why can't I successfully compare encrypted object text when comparing SQL Server 2005 and 2008 databases?
SQL Compare 3.0 introduced seamless decryption of encrypted stored procedures, views, and user-defined functions in SQL 2000, however, this functionality did not work with SQL Server 2005 because of improved encryption methods in the SQL Server engine.
If you have got a SQL Server 2005 database and you are given the message "the object's text is encrypted" in the place of a SQL query in the SQL code window, upgrading to SQL Compare 7.1 and up will solve this issue, provided the option to compare encrypted objects is selected in the project options.
Like in SQL 2000 object decryption, elevated permissions on the database server are required to decrypt database objects. This typically means that the account you use to connect to the database with will have to be a member of the SYSADMIN or SECURITYADMIN roles.
To test whether or not you have high enough permissions on SQL Server 2005 and 2008, running these queries successfully would be a good indication:
DBCC DBINFO
DBCC PAGE
