SQL Compare 9

Understanding the comparison results

This topic provides information that may help you to understand your comparison results.

Objects with different owners or schemas

Objects that are the same but have different owners or schemas are treated as different objects. For example, if a stored procedure exists in both databases and is identical except for its owner, it is considered to be a completely different object.

If you want to compare objects that are the same but have different owners, you can remap the owners when you set up your comparison project.

Objects covered by migration scripts

Objects that are covered by migration scripts are indicated in the comparison results with an icon:

For more information, see: Synchronizing with migration scripts.

You can view the migration script that covers the object by right-clicking the object in the comparison results, and then clicking View Migration Script.

Database-level permissions

For SQL Server 2000, differences in database-level permissions are not detected by SQL Compare. For example, if you have used SQL Server Enterprise Manager to set up permissions for your database, such as GRANT CONNECT or GRANT BACKUP, those permissions are not considered; however, permissions on objects are detected. If you want to include database-level permissions in your comparison project, it is recommended that you use roles.

For SQL Server 2008 and SQL Server 2005, differences in database-level permissions are detected.

Object-level permissions

When you connect to a database for which your user is not the database owner, SQL Compare displays only those objects for which you have been granted permissions. In addition, the SQL Differences pane will display only the object-level permissions for the authenticated user; other users' permissions will not be displayed.

Note that the synchronization script may fail if the authenticated user does not have full object-level permissions.

If a permission is given using WITH GRANT OPTION (for example GRANT SELECT ON <objectname> TO <username> WITH GRANT OPTION) it will not be recognized. Permissions assigned that do not use WITH GRANT OPTION are detected.

Extended stored procedures

SQL Compare does not compare extended stored procedures.

Encrypted database objects

If you are comparing a SQL Server 2000 database that contains an encrypted user-defined function, stored procedure, trigger, or view and you have system administrator permissions, SQL Compare decrypts the object and you can view its internal SQL in the SQL Differences pane of the main window.

In SQL Compare version 7.1 (and later) you have the option to decrypt text objects in SQL Server 2008 and SQL Server 2005 databases created using the WITH ENCRYPTION option.

Disabling this option can result in faster performance. To disable this option, on the Options tab of the Project Configuration dialog box, clear the Decrypt encrypted objects on 2005 and 2008 databases check box.

When this option is disabled, SQL Compare cannot compare the encrypted objects, or display their creation SQL scripts, and cannot synchronize them.

SQL Compare version 7.0 (and earlier) cannot decrypt objects that are encrypted in a SQL Server 2005 or SQL Server 2008 database. If an encrypted object which cannot be decrypted exists in both databases, it is shown under the objects that exist in both but are different group in the comparison results in the main window (select Type of Difference in theGroup by box to arrange objects by difference). SQL Compare cannot compare the encrypted objects, or display their creation SQL scripts, and cannot synchronize them.

Certificates, symmetric keys, and asymmetric keys

SQL Server severely restricts access to certificates, symmetric keys, and asymmetric keys. Consequently, SQL Compare cannot compare all of the properties for a symmetric key.

To ignore all certificates, symmetric keys, and asymmetric keys in the synchronization, on the Project Configuration dialog box, select the project option Ignore certificates, symmetric and asymmetric keys.

Replication

SQL Compare does not compare stored procedures that have been created for replication.


Didn't find what you were looking for?