In addition to the account that a user needs to be authenticated for logging in to a database, database systems validate the permissions that a user has for accessing and working with individual databases. The activities a user is allowed to perform are determined by the permissions assigned to the user account from which they gained access to the database.
The StarQuest Data Replicator fully supports the host's data access security. It accesses host data through an ODBC data source, and each data source is configured with a host user name and password. The host database administrator manages the access rights for the host database accounts, so the Data Replicator can perform only the database operations that are permitted by the host account.
Regardless of which SQL Server authentication mode you use to connect to an instance of SQL Server, make sure that each login that Data Replicator will use is assigned to the database you want that login to use. In addition, make sure that the login account has permissions that allow it to perform the necessary tasks, as specified below:
Source connections require permission to SELECT any source table columns or views that will be replicated.
Destination connections require appropriate permissions to work with the data and execute procedures, including the following permissions:
SELECT table
INSERT table
UPDATE table
DELETE table
CREATE table
Depending on the destination options that are defined for the subscription, the user ID also may need permission to DROP the table. If the table has an index, the user ID also may need permission to alter tables and drop and create indexes. To alter source data either before or after replication, the user ID needs any privileges required by the SQL processing statements defined for the subscription.
Control database connections require all of the permissions listed above for destination connections, plus permission to DROP and UPDATE tables and indexes.