Macro is returning "Create Table" permission is denied for the WRONG database

[mkav9893]

Hi,

I am currently developing a macro that our team can use to write tables to our sandbox. Using this macro will allow the team to easily write to the sandbox, and will also allow us to monitor sandbox size, properly track who created the table, and the dates it was created so we can have a better sandbox management. It will also tile out the incoming file to batch the writing into 5 separate appends.  

The macro, attached, writes to the sandbox database. However, when testing it out - it returns an error for a different database, that I do not have write permissions for. I dont understand why its returning this because I can utilize the write to DB for the specified sandbox table on its own, but when editing the macro I receive the "Create table permission denied" for a different database. The error logs, as seen below return edw_NAME. In my macro I DO NOT reference edw_NAME, I only reference edw_sandbox.sandboxprivate. I do have Write permissions to the edw_Sanbox.  I have also attached the macro in question as well. Please help!

SandboxWriteTest (1) Tool #33: Executing PreSQL: "WITH "Tool75_35d2" AS (SELECT * FROM "##AYXdab1f4cf82fe24d99114260c2be445e8"), "Tool6True_5de3" AS (SELECT * FROM "Tool75_35d..." : Microsoft SQL Server Native Client 11.0: CREATE TABLE permission denied in database 'edw_NAME'.\42000 = 262
SandboxWriteTest (1) Tool #33: Error running PreSQL on "NoTable": Microsoft SQL Server Native Client 11.0: CREATE TABLE permission denied in database 'edw_NAME'.\42000 = 262
SandboxWriteTest (1) Tool #27: Error opening table: Microsoft SQL Server Native Client 11.0: Invalid object name 'edw_sandbox.sandboxprivate.sandboxtabletest'.\42S02 = 208
SandboxWriteTest (1) Tool #21: Error opening table: Microsoft SQL Server Native Client 11.0: Invalid object name 'edw_sandbox.sandboxprivate.sandboxtabletest'.\42S02 = 208
SandboxWriteTest (1) Tool #35: Error opening table: Microsoft SQL Server Native Client 11.0: Invalid object name 'edw_sandbox.sandboxprivate.sandboxtabletest'.\42S02 = 208
SandboxWriteTest (1) Tool #15: Error opening table: Microsoft SQL Server Native Client 11.0: Invalid object name 'edw_sandbox.sandboxprivate.sandboxtabletest'.\42S02 = 208

[Claje]

Hi,

IN-DB workflows are particularly challenging to debug for issues like this, since the connection string is (understandably) not available, and the workflow cannot be run, so we are debugging exclusively based on code.

I'm guessing that the "edwdb" connection you are using has a default database of edw_NAME, which is causing the issue.  I have found that this can lead to unexpected behaviors in cases where the table is being created in a different database than the default.

One possible workaround (that works with regular tools, but may not work with In-DB in this context) is to try including brackets '[]' around the database, schema, and table names.  I have found that creating this more explicit syntax can potentially resolve the issue.

[mkav9893]

Unfortunately adding the brackets did not correct the issue. 

Is there any way to configure the default database under a specific database connection via the connection string?

Or alternatively, can I write to the database without using an in-db tool?

[Claje]

Hi,

You can write to the database using a regular Output Data tool - if you have data in an in-database stream you will need to use a Data Stream Out tool first.

I think to change the default database you need a separate connection string entirely.

[mkav9893]

I've attempted to convert the macro to be non-inDB and i am returning the same permission denied error.

Do you have any thoughts or ideas on how to modify the connection string to read in a different database?

[Claje · Accepted solution]

Hi,

This might not be exact, but should give you an idea as to what to modify in your connection.

Replace YOURDATABASENAME and YOURSERVERNAME in the below string with the appropriate information.

odb:Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=YOURDATABASENAME;Data Source=YOURSERVERNAME;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID="";Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE