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

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

SandboxWriteTest.yxmc

Error Message

Macros

Batch Macro

Help

Database Connection

Iterative Macro

Accepted answers

Claje

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

All comments

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?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703