Thanks for the explaination.
Could you please explain what do you mean by "third party" db mentioned in your comment.
Also can you explain why do we need to grant authority to macro db?I mean what is the purpose.
I am new to this.Searched alot for the TPUMP flow.
It would be very helpful if you reply to my above queries.
By "third party", I mean the macros are not in the user database for the username that TPump / Stream is logged on with, nor are they in the same database as the target table; they are in a "third" database.
Let's say TPumpUser logs on and wants to modify TableDB.MyTable but place the macros in MacroDB. Obviously TPumpUser needs CREATE MACRO right on MacroDB, and rights to execute the statements in the body of the macros, or the CREATE will fail. And as the creator, TPumpUser will be given EXEC (macro) right automatically.
But at execution time, the only right a macro caller (TPumpUser) needs is EXEC on the macro. It's the rights of the "owner" MacroDB on TableDB.MyTable (or TableDB) that are checked for executing the statements within the macro. And because the caller is not the owner, MacroDB is attempting to provide (indirect) access to another user; that is only permitted if MacroDB holds the rights "WITH GRANT OPTION".