SysAdmin can't delete from table or truncate table

I have an autosys job that runs and SSIS package under a service account.  The package truncates the table and then loads data from a flat file.  The service account is sysadmin on the server, but I keep getting the following error message:
Description: Executing the query "delete from tablename" failed with the following error: "The DELETE permission was denied on the object 'tablename', database 'db_name', schema 'dbo'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any ideas of how I can get this to work?  This same id is working on this server and truncating/deleting other tables in the same database.  Does the sysadmin role give the account access to delete from or truncate any table in any database on a server?  Are there any limitations on this role?
seasonmmclaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> The service account is sysadmin on the server

Service account might be sysadmin, but kindly check what is the user account under which this package is executing..
Suspect that its executing under some other user account.

>> Does the sysadmin role give the account access to delete from or truncate any table in any database on a server?  Are there any limitations on this role?

No limitations for sysadmin role, Ideally sysadmin role can do anything in the server instance.
0
seasonmmclaneAuthor Commented:
In the log, it says that it is being run under this account.  We are able to log in and run a delete or truncate command manually...
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Is there any exclusive locks hold on that table at the time you are trying..
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

seasonmmclaneAuthor Commented:
None that I know of...
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Any other strange observations during the delete operation..
Does your sysadmin not able to delete or truncate table during all times or is it only during specific time..
0
seasonmmclaneAuthor Commented:
The account is able to delete and truncate when the logged into the server, but not when run as an autosys job.  The log confirms that it is using the account.  
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Are you sure that the table you are trying to truncate is in your server..
Check whether it is present in your Server or is a distributed query involving remote servers..
0
seasonmmclaneAuthor Commented:
Ok, I FINALLY figured it out!  I looked at the configuration file and it turns out that I had the wrong server in the config file.  So, even though the package was running on the right server, it was referencing a table on a different server.  I got the config file fixed with the correct server and it worked.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Glad you identified it and fixed it out..
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.