I am attempting to insert data from an uploaded excel spreadsheet, and I'm getting the following error.
System.Data.SqlClient.SqlException (0x80131904): Cannot find the object "data_import" because it does not exist or you do not have permissions.
I can run the stored procedure fine within SSMS, but when attempting to test the end to end process, I'm receiving this error.
The process is as follows:
User uploads an Excel file, and the restful API takes the file and writes it to the file system that the API is running on, and send a message to a message queue
A windows service is polling the queue, and when it finds a message, it attempts to connect to the database, and run the stored procedure.
The service is connecting to the database, and beings executing the stored procedure, but when it hits the first statement that contains SQL code, it fails and the following error is thrown.
This is the line in question.
if (select count(*) from dbo.data_import) > 0 begin PRINT N'The import table has old data. Removing data...' truncate table dbo.data_import -- THIS IS WHERE THE ERROR IS THROWN end
I'm getting the
I'm guessing it has something to do with permissions, but that just a shot in the dark.
Any help would be appreciated.
The user needs the "ALTER" permission on the table to perform a TRUNCATE operation even in an SP. You can either give the user executing the SP permission to TRUNCATE or add the "EXECUTE AS " where "" has that permission.
I know this was asked two years ago, but I had the same issue and it took some digging for me to find this. A comment from David helped me track it down. So even though the statement is being made within the SP, the user still needs the "ALTER" permission on that table because it's altering the table's definition (in truncate's case, it's resetting the table's identity value). I had a similar issue with a TRUNCATE and an sp_rename command when swapping tables in an SP.
User contributions licensed under CC BY-SA 3.0