Permissions required to use SQL Compare
Published 04 June 2013
Comparing
The permissions you need to compare databases are:
| SQL Server version | Recommended permissions | Minimum permissions | Permissions if you want to compare encrypted objects |
|---|---|---|---|
| SQL Server 2008 and later |
| VIEW DEFINITION permission |
|
| SQL Server 2005 | db_owner role | VIEW DEFINITION permission | sysadmin role |
| SQL Server 2000 | db_owner role | public role | db_owner role |
Deploying
For deployments, we recommend using the db_owner role. However, it's possible to deploy with just the permissions to make the changes listed in the Summary tab (on the Review page of the deployment wizard).
Exactly which permissions you need depends on the objects in your databases, and what you're deploying.
You can't deploy objects that are encrypted, if they're encrypted in the source.
Permissions issues
If you don't have the right permissions:
- Some objects may be missing from the comparison results.
For example, User Defined Types don't appear in the comparison results if you don't have permission to view the schema they belong to. - Deployments and deployment scripts may fail or have unexpected results.
If a deployment fails, SQL Compare attempts to roll back the changes, using transactions. However, in some circumstances, rolling back isn't possible.
