Permissions required to use SQL Compare

SQL Compare

Latest version: 10.0

Knowledge base

Permissions required to use SQL Compare

Category: Security & data protection

Date: 02 Feb 2009

Product: SQL Compare

The permissions required to compare and synchronize a database using SQL Compare depend on the objects in your database schema and the version of SQL Server you are using.

For example, a user belonging to the PUBLIC role can compare a SQL Server 2000 database. However, they can not compare encrypted stored procedures without dbo permissions.

To perform a synchronization, you are recommended to have dbo permissions.

If you create a synchronization script to run later, you are also recommended to run the script as a dbo user. This ensures that any objects created will have the correct schema, permissions, and authorizations.

Note that:

If you have insufficient permissions, some objects may be missing from the comparison results. For example, User Defined Types do not appear in the comparison results if you do not have permissions for the schema they belong to.
A synchronization script generated from incomplete comparison results may fail or produce unexpected results. For example, the schema may refer to a User Defined Type that does not exist.
If you do not have dbo rights, granting the VIEW DEFINITION permission is sufficient to compare unencrypted objects in SQL Server 2008 and SQL Server 2005 databases.
However, sysadmin permissions are required to decrypt encrypted stored procedures.
If you are using SQL Server 2008, you are recommended to have SELECT permission for the system view sys.sql_expression_dependencies. You may experience poor performance when comparing databases if you do not have this permission.
If you are using SQL Server 2008, you may require VIEW SERVER STATE permissions to compare some encrypted objects.
When synchronizing, a user must have permission to make all of the modifications listed in the Summary tab at the end of the Synchronization Wizard, or the synchronization may fail.
If a synchronization fails, in most circumstances changes are rolled back. SQL Compare uses transactions to do this. However, there are some circumstances in which this is not possible.

For more general information on permissions, refer to your SQL Server documentation.

Was this article helpful?

Search support