Determining minimum database permissions for SQL Data Compare

Category: Security & data protection
Date: 19 Nov 2008
Product: SQL Data Compare
What are the minimum permissions required in a database for the comparison and synchronization of Data using SQL Data Compare?

SQL Data Compare can compare and synchronize data in tables and views of a SQL Server database. In order to do this effectively, the user connecting to the database needs access to the schema (data definitions) and the data objects of a database, and may require additional privileges if the data is being synchronized with or without the use of some of the options available for controlling the synchronization behavior.

In SQL Server 2000, it is only necessary to be a member of the PUBLIC role in order to gather information about the database schema. To compare data, SELECT permissions are required on all objects whose data is being compared. The easiest way to grant SELECT permissions on all objects is to add the SQL Data Compare user to the database's db_datareader built-in role. To update the database being compared, it is necessary to have INSERT and UPDATE rights granted in addition to SELECT. The easiest way to grant these permissions for all objects in the database is to add the user to the db_datawriter user role.

There is one special circumstance that requires elevated privileges, and that is when the 'disable foreign keys', 'Drop primary keys, indexes, and unique constraints', 'Disable DML triggers', 'Disable DDL triggers', or 'Reseed identity columns' options are used. These actions require access Data Definition Language modification, so it may be necessary to add the user to the db_ddladmin role or DBO role to allow the synchronization to succeed if any of the specified options are configured.

Under SQL Server 2005 and 2008, the ability to view schema may be restricted for any users with PUBLIC role membership if the VIEW DEFINITION right has been revoked. One must have VIEW DEFINITION permission granted in order to see the definition of objects in the database.

Document ID: KB200802000227 Keywords: Minimum,user,permissions,Compare,Data,synchronize

Was this article helpful?

Search support