Microsoft SQL Server Topics

Syniti Replicate supports use of Microsoft SQL Server for:

When setting up SQL Server for use with the Log Server Agent or the transaction log:

  • Define two connections to the database: a non-system administrator connection  and a system administrator connection. (DBMS tools)

  • Set up the distributor (Management Center)

  • Establish primary keys on tables (or use trigger-based replication option.) (Management Center or DBMS tools)

This section contains topics that describe:

Syniti Replicate Permissions to enable Trigger-Based Replications for SQL Server Sources

To create a trigger-based replication using SQL Server as the source, the following permissions are required on the source connection:

  • Permission to create connections

  • Create table privileges when creating the source SQL connection, so that the master table can be created in the database you choose on that instance.

    NOTE: This is only required when the connection is initially created in Syniti Replicate. After the master table is created, you can revoke the privilege.

  • Table-level ALTER permissions to create the replications, which allows the creation of the triggers. These permissions are required each time a replication is created. If, at some point no other replications will be created, that table-level ALTER permission can be revoked.

NOTE: The security context of a trigger, unless otherwise specified on the trigger's definition, is the context of the principal that caused the trigger to fire (i.e., the caller). Meaning that if the changes on the SQL source are caused by a user called SAPuser, then that user must be able to insert, update and delete records from the master table that is created or chosen when the connection is created.

In other words, the user that will write records on the master table is not really going to be the Syniti Replicate user. The Syniti Replicate user creates the triggers on the source tables, but the data is inserted by whoever made the actual change on the source table. This is usually acceptable because it's a user that's db_owner on most databases when they're application users, but it's good to keep in mind.

If there are special requirements about the above (the security principal that actually writes data to the master table), you could alter the trigger directly in SQL Server once it's created and use an execute as clause. For example CREATE TRIGGER dbo.triggername1 WITH EXECUTE AS OWNER, or CREATE TRIGGER dbo.triggername1 WITH EXECUTE AS HOSTNAME\Username (if using a specific user, that user must have IMPERSONATE permissions to call it from an EXECUTE AS clause).

Further Resources