These pages cover SQL Compare versions 10.0 to 10.7. Help for older versions is also available.

We've released a beta version of the improved migration script feature. We'd love to hear what you think.

Skip to end of metadata
Go to start of metadata

The permissions required to compare and deploy 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 deployment, we recommend you have dbo permissions.

If you create a deployment 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.

Icon
  • 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 deployment 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, we recommend you have SELECT permission for the system viewsys.sql_expression_dependencies. You may experience poor performance when comparing databases if you don't have this permission.
  • If you are using SQL Server 2008, you may require VIEW SERVER STATE permissions to compare some encrypted objects.
  • When deploying, a user must have permission to make all of the modifications listed in the Summary tab at the end of the deployment wizard, or the deployment may fail. If a deployment 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, see your SQL Server documentation.

  • No labels