Sybase Adaptive Server Anywhere: Setting Up Source User Privileges

When we use Sybase SQL Anywhere as a source database in the SQLWays Wizard tool the user that we specify to connect to our source database should have appropriate rights to read information from all the system tables. You can use a user that already exists in your source database and grant him the required privileges or create the new one and assign appropriate privileges to him. So to create a new user you can use the statements below. For Sybase 11 version and higher you can use CREATE USER statement:

• create user test_user identified by password;

For the Sybase Anywhere 10 version and lower you need to use GRANT statement to create a user and grant the required privileges.

• GRANT CONNECT to test_user IDENTIFIED BY sql

The user that you will use should have select privilege on the SYS.sysuserperm, SYS.sysprocedure, SYS.sysviews, SYS.systable, SYS.sysprocparm, sysdomain, SYS.systrigger, sysgroup, sysusers, sysusertype, sys.syscolumn, sys.sysindex, sys.sysixcol, sys.sysfkcol, sys.sysforeignkey, sys.sysconstraint, sys.sysevent, sys.syseventtype, sys.sysschedule tables. So to assign the select privilege you need to execute GRANT statement for all these system tables.

This query generate and execute grants for the required system tables and views:

BEGIN
DECLARE @table_name VARCHAR(255);
DECLARE @sql_query VARCHAR(500);
DECLARE @cursor CURSOR FOR
SELECT 'SYS.sysuserperm' UNION ALL
SELECT 'SYS.sysprocedure' UNION ALL
SELECT 'SYS.sysviews' UNION ALL
SELECT 'SYS.systable' UNION ALL
SELECT 'SYS.sysprocparm' UNION ALL
SELECT 'sysdomain' UNION ALL
SELECT 'SYS.systrigger' UNION ALL
SELECT 'sysgroup' UNION ALL
SELECT 'sysusers' UNION ALL
SELECT 'sysusertype' UNION ALL
SELECT 'SYS.syscolumn' UNION ALL
SELECT 'SYS.sysindex' UNION ALL
SELECT 'SYS.sysixcol' UNION ALL
SELECT 'SYS.sysfkcol' UNION ALL
SELECT 'SYS.sysforeignkey' UNION ALL
SELECT 'SYS.sysconstraint' UNION ALL
SELECT 'SYS.sysevent' UNION ALL
SELECT 'SYS.syseventtype' UNION ALL
SELECT 'SYS.sysschedule';

OPEN @cursor;

FETCH NEXT @cursor INTO @table_name;
WHILE SQLCODE = 0 LOOP
    SET @sql_query = 'GRANT SELECT ON ' || @table_name || ' TO YOUR_USER';
    EXECUTE IMMEDIATE @sql_query;

    FETCH NEXT @cursor INTO @table_name;
END LOOP;

CLOSE @cursor;

END;

You also need to provide a GRANT SELECT for all the tables you are going to migrate so that our tool can extract data from them. You can use the following query to do this:

BEGIN
DECLARE @table_name VARCHAR(255);
DECLARE @sql_query VARCHAR(500);
DECLARE @cursor CURSOR FOR
SELECT table_name
FROM SYS.SYSTABLE
WHERE creator = 1 and table_type='BASE';

OPEN @cursor;

FETCH NEXT @cursor INTO @table_name;
WHILE SQLCODE = 0 LOOP
    SET @sql_query = 'GRANT SELECT ON ' || @table_name || ' TO YOUR_USER';
    EXECUTE IMMEDIATE @sql_query;

    FETCH NEXT @cursor INTO @table_name;
END LOOP;

CLOSE @cursor;

END;

Note: replace YOUR_USER with the username you will use to perform the migration.

OR

You can just initiate GRANT DBA TO YOUR_USER

If you have any other questions, please contact us: support@ispirer.com