Sybase IQ replicate database permissions

To apply transactions in a replicate database, Replication Server requires a maintenance user ID that you specify using the Replication Server create connection command.

Before replication can start, you must define the maintenance user ID at the Sybase IQ data server and grant authority to the ID to apply transactions in the replicate database. The maintenance user ID must have these permissions in the Sybase IQ replicate database:

• RESOURCE authority to create work tables and temporary indexes.

• EXECUTE permission to run the sp_iqwho stored procedure.

• GRANT ALL permission on all replicate tables.

• UPDATE authority on all replicate tables and EXECUTE authority on all replicate stored procedures.

You can grant DBA and RESOURCE authority if you are starting with a simple set up or are testing replication to Sybase IQ.

1. Create the maintenance user for Sybase IQ with relevant privileges To create the maintenance user, use the Sybase IQ rssetup.sql sample script:

   WARNING! If there is already a maintenance user ID, the script will reset the password to the default password.

   grant connect to dbmaint identified by dbmaint
   grant DBA to dbmaint
   grant membership in group rs_systabgroup to dbmaint
   
   -- Create a user for REPSRV to extract -- materialization data, etc.
   -- Give sa user access to any replicated tables
   -- Give sa user access to REPSRV schema
   grant connect to sa identified by sysadmin
   grant DBA to sa
   grant membership in group rs_systabgroup to sa
   
   -- Allow sa and dbmaint to reference replicated tables created by DBA
   grant group to DBA
   grant membership in group DBA to dbmaint
   grant membership in group DBA to sa
   go
   

   You can find the sample script in the scripts directory within the Sybase IQ installation directory. For example, on UNIX platforms in:

   • Sybase IQ versions earlier than 15.0 – /$ASDIR/scripts

   • Sybase IQ 15.0 and later – /$IQDIR15/scripts

   See the Sybase IQ Installation and Configuration Guide for locations of directories.

2. The IQ DBA must verify that the Sybase IQ database is compatible with Transact-SQL®. See “Transact-SQL compatibility options,” in Chapter 2, “Database Options” in the Sybase IQ Reference: Statements and Options and Appendix A, “Compatibility with Other Sybase Databases” in the Sybase IQ Reference: Building Blocks, Tables, and Procedures.

3. Grant the appropriate permissions to all tables and stored procedures that are to participate in replication.