Create Table error when executing a stored procedure
I have a stored procedure which does a lot of things, one of which is executing multiple create table AS statements. Now the error that is coming is that the ETL user that I have created to run this procedure, apparently, doesn't have CREATE TABLE rights on the target database. Now the scenario is futher explained below:
- Target DB X is where tables are to be created.
- Another DB Y is where the stored procedure is placed.
- ETL user has direct Create Table rights on DB X, and DB Y has CREATE TABLE with grant option rights on DB X.
The SP still fails saying user ETL does not have create table rights to DB X. What am I doing wrong here :S?