?
Solved

SysAdmin can't delete from table or truncate table

Posted on 2010-03-29
9
Medium Priority
?
650 Views
Last Modified: 2013-11-30
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?
0
Comment
Question by:seasonmmclane
  • 5
  • 4
9 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29032572
>> 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
 

Author Comment

by:seasonmmclane
ID: 29209036
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29219123
Is there any exclusive locks hold on that table at the time you are trying..
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:seasonmmclane
ID: 29258320
None that I know of...
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29461584
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
 

Author Comment

by:seasonmmclane
ID: 29748714
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 1200 total points
ID: 29807777
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
 

Accepted Solution

by:
seasonmmclane earned 0 total points
ID: 29814929
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 29819891
Glad you identified it and fixed it out..
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question