User cannot insert into Temp Table

_{Thursday, March 30, 2017 1:52 PM}

I have a 3rd party tool that is calling a Stored Procedure on my DB.
The call was failing, and unfortunately the application does not surface much more error detail then 'An error has occurred'.
So I resorted back to trial-and-error approach.
Now I have my test case identified.
In the below code, the code succeeds without the insert line, but fails when it is included.

create table #Run (Fruit int)
insert into  #Run (Fruit) values (2)

That all suggests that the user  has permission to create a temp table, but not to insert into it !?
So now I am lost !!

Any ideas where to start with this one ?

_{Thursday, March 30, 2017 4:08 PM ✅Answered}

create table #Run (Fruit int)
insert into  #Run (Fruit) values (2)

That all suggests that the user  has permission to create a temp table, but not to insert into it !?

All users have permission to create a temporary table and once it is created have all permissions on that table.  So it is definitely not the case that the user does not have permission to insert into the temp table.

So it is something else.

One possibility is you have errors that show up due to the data.  So when there is no data, you don't get an error.  For example, the following will get a divide by zero error, but if you comment out the insert statement, it runs without an error.

Declare @Variable int;
Set @Variable = 0;
Create Table #Run(Fruit int)
Insert #Run(Fruit) Values (2);
Select Fruit / @Variable From #Run ;

There are other possibilities as well.  It it difficult to give you much help without more information.

One way to tell for sure whether it is the insert that fails or the procedure fails because of something else would be to change the code to be

Create Table #Run(Fruit int)
Insert #Run(Fruit) Values (2);
Delete From #Run;

If that runs OK, you know the insert statement doesn't fail, but it is some other problem.

Tom

_{Thursday, March 30, 2017 2:04 PM}

but fails when it is included.

So just like your application, you leave the reader to wonder what "fails" means. I suspect that your script generates an error when you run it multiple times - perhaps because you commented out one line first and then added it back (or vice versa). So it would help for you to provide context as well as define more precisely what "fails" means. If an error occurs, then post the complete error message.

_{Thursday, March 30, 2017 2:12 PM}

Additional, you can use SQL Profiler to trace the error which the application cause, see Troubleshooting and Analysis with Traces

Olaf Helper

[ Blog] [ Xing] [ MVP]

_{Thursday, March 30, 2017 2:13 PM}

Here is the application output : 

2:59:30 TT

Output cleared.

2:59:34 TT

Started loading data

The following error occurred:

Connector reply error: ***

The error occurred here:

?

Data has not been loaded. Please correct the error and try loading again.

_{Thursday, March 30, 2017 2:15 PM}

Thanks Olaf, 

Unfortunately, I work in a heavily locked down environment where I do not have easy access to such tools.

Accessing the tools I need,  requires a delegation to the King/Queen and more negotiations than Brexit ;)

_{Thursday, March 30, 2017 2:42 PM}

That does not help. You said your script failed. What is the error message when it fails? Or how exactly do you know YOUR SCRIPT (not your application) failed?

Alternatively, contact your tool vendor for support.

_{Thursday, March 30, 2017 3:03 PM}

Scott, I said in the very beginning that 'the call' fails, not my script.

I also said that the Error message was useless, so not sure why you are complaining that it's useless now that I have posted on request.

Let's reword the Question :

What permissions would affect a user's ability to insert into a temporary table ?

I know that 'contact' vendor is a logical and valid answer ... but sometimes in life it isn't that easy or hassle free when you work in a large organisation. You can argue that all you like - that's just the way it is.

I was maybe hoping that someone with experience in this area might have some across similar tempDB issues before.