Permissions required to use SQL Compare
Published 04 June 2013
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.
- 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 DEFINITIONpermission is sufficient to compare unencrypted objects in SQL Server 2005 or later databases. However, sysadmin permissions are required to decrypt encrypted stored procedures. - If you're using SQL Server 2008 or later, we recommend you have
SELECTpermission for the system viewsys.sql_expression_dependencies. You may experience poor performance when comparing databases if you don't have this permission. - If you're using SQL Server 2008 or later, you may require
VIEW SERVER STATEpermissions 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.
