Pls hlep: Access rights in combination with DBC.SysExecSQL()
I'm writing a stored proc with the purpose to count rows of tables with special group by clauses and store the result an a Tools-Database for further analysis.
I decided to use an SP because the count-SQL-Statements are like this: "INSERT INTO TOOLS.TabList (TableName, RowCount,...) SELECT count(*),... FROM )", and these SQL-Statemens are generated by a view, that returns aproximately 3500 rows. I only need to read the SQL-Statement from the cursor based on that view into a variable (e.g. sqlStmnt) and run it against the DB.
To run the statement, I use "CALL DBC.SysExecSQL(:sqlStmnt)"
The tables to be counted reside in several databases under the same main database as the TOOLS database. The TOOLS database definitly has SELECT WITH GRANT OPTION on these other databases.
What's missing? Does DBC also need to have SELECT WITH GRANT OPTION on the other databases?
Re: Pls hlep: Access rights in combination with DBC.SysExecSQL()
okay, we've found out what is necessary. As there's no hint at all over the Internet (as far as I can tell...), I want to share the solution with you:
It is necessary, that the stored procedure which is aimed to select data from outher databases, is CREATED not in the TOOLS databse but in the Permspace of the User, who will run it. Still the TOOLS database needs to have SELECT WITH GRANT OPTIONS to the other databases. Now it works.