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?