February 15, 2015 at 7:29 am
I have a package on the default instance which runs and completes successfully. When that package is moved to the same SQL server, but a different instance, running under the same service account, it fails. The error is below with some specific stuff removed:
Delete Data from Level 1:Error: Executing the query "-- Variable to capture FileID's
DECLARE @DeleteFil..." failed with the following error: "The DELETE permission was denied on the object '[name removed]', database '[]', schema '[]'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
This makes me think that the package under the non-default instance ends up running under a different security context. I would appreciate any help/suggestions on how to proceed to troubleshoot this and is possible some explanation of why this would happen.
February 15, 2015 at 3:30 pm
It's most likely that a security issue is in question, but can you check that the service accounts have the same privileges. Also, you should give some more explanation of what that package or parts of it do. Problem is not very clear to readers.
Igor Micev,My blog: www.igormicev.com
February 15, 2015 at 3:51 pm
If you are using windows authentication for you connection strings, the account it is running under does not have permission. You can do 1 of 3 things.
1. Either grant permission to the Windows Authentication account running the job.
2. Change your connection strings to use SQL Authentication
3. Create a proxy account to run the package.
Hope this helps.
February 16, 2015 at 2:39 am
How is your package scheduled/executed?
If you use SQL Server Agent, it's not the service account of SQL Server that is used, but the service account of Agent.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 16, 2015 at 7:23 am
In response to Igor:
The service account has the exact same privileges. The package take data from one table and moves it into another depending on some criteria. While it does that, it clears out the destination table with a delete statement.
In response to Koen:
The package is in a job, which I run manually. Each package is setup the same way.
February 16, 2015 at 8:53 am
Does the service account of Agent have access to delete the records from database?
Regards
Durai Nagarajan
February 17, 2015 at 9:01 am
Well, my expectation was that since the package was setup the same way and the service account has the exact same privileges on the default instance and the non-default instance, I would get the same results. I am not sure why the expectation should not be that. That's what I was trying to understand.
I gave the service account specific rights to the table and I don't get the error any longer, but this presents a maintenance problem. I would have to give individual rights to each table that the package impacts. I hope am making sense in not wanting to have to do this.
February 17, 2015 at 10:06 am
As is mentioned above its a security issue. And you have many options:
1. Add the Service Account used by the SQL Agent as a login on each of the servers, add the login as a user in each database it needs to do the activity, add the user to a suitable fixed database role.
2. Add the Service Account used by the SQL Agent as a login on each of the servers, add the login as a user in ach database it needs to do the activity, add the user to a suitable user defined database role, Add the required table permissions for the user defined role.
3. Use a proxy account that already has the required permissions on all the servers
There is more, but these are 3 fairly basic options.
MCITP SQL 2005, MCSA SQL 2012
February 18, 2015 at 4:44 pm
Thank you all. I appreciate everyone's help and advice.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply