March 5, 2009 at 1:33 pm
Hi guys,
i have one job that has few CmdExec steps and few tsql steps.
CmdExec steps are basicaly copying few csv files from and to a remote server.
The service account for sql server agent has no access to the remote server. Due to some security reason i cannot add this account to the remote server.
All of my CmdExec command steps failed because of the permission denied error.
I solved this issue by setting up a proxy account to the agent ( that account is also there in the remote server ). Since i need to initate the job through the proxy account i changed the owner of the job to a non sysadmin account. Now all of my Cmd commands work fine.But now my problem is since i used a non sysadmin account for the job owner all of my tsql steps failed becuase of permission denied on few of the objects in the database. I have to get both cmdExec and tsql steps initiated throough the proxy account. Is any way to do that?
All of your comments and thoughts will be greatly appreciatable.
Thanks
Ichayan
March 5, 2009 at 2:12 pm
The proxy account can you not grant it access to the objects in question?
Or another way is to change the execution context of the TSQL statements so it is using someone who has access to the db. And you'll have to make sure proxy account has impersonation rights.
I thinks...
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 6, 2009 at 12:59 pm
Thanks for the reply...
I cannot give access to the objects in question for this proxy account.
Do you think any other possible way.........
Thanks
March 6, 2009 at 7:07 pm
Options?
1) are TSQL have to be executed at a time after the CSV file move? Or is it mixed together?
- If it is after, then schedule one job to CSV move with proxy account to remote server.
- And do second job run your TSQL.
2) Create a Store Proc doing the TSQL actions and change execution context with in Store Proc to the account that does have access.
- New Store Proc either in a DBManagement database or the current database.
- Create proc using the account that has access to objects in question.
- In store proc make sure the context is set to that user.
- Give the proxy account execute permission to the SP to limit the access.
Those are the only two options i can think off ...
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2009 at 4:55 am
Thank you so much.............
I will try second option.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply