Setting up a Proxy account 1

[Zukkster]

I want users to be able to run DTS packages to export data from the database.

I know that I need to set up a Proxy account and give the proxy account execute rights for the XP_cmdshell stored procedure, to allow non-members of the sysadmin role can run the following SQL statement to execute the DTS package.

exec master.dbo.xp_cmdshell 'DTSRun /S CMCUKSQL2000 /N REPORTER: Export Redeemers /E'

I just don't know where to start with setting up the Proxy account.

Does the Proxy account need to be a network login account? How and where do I set up the proxy account in SQL Server? Is it as a user in the Master db?

I've read that I need to map the users to the proxy account. How do I do this?

I know that I go to the 'Job System' tab in the SQL Server Agent, and input the details of the proxy account (once it's set up_. I just don't know how to set up the proxy account and I've got a feeling I need to involve the network administrator, but don't know what to tell him to do.

Thanks

 

[dafdba]

You can set the proxy account in the Enterprise Manager in the properties of SQL Server Agent (under Management) under the Job System tab. Unmark the "Only users with sysadmin..." under Non-SysAdmin job step proxy account. You will get a box to fill in a Username and password. You must fill in a windows user which has permissions on the server and in sql server (for example a user from the local admin group on the server).

From that moment all steps in a job run by SQL Server agent (except the TSQL steps) will use the Proxy User account.

If you want to allow other users then users with sa rights to run jobs, you have to give these users the public role of the msdb database and change the ownership of such a job to the specific user.

Be very carefull with this, because from this moment the user with the public role of the msdb can create his own dts packages and his own jobs. From a dts package started in a job with dtsrun he will have the same permissions in the database as the user which is set as proxy account.

When you create a standard role in the msdb database with a deny on the following stored procedures: sp_add_dtspackage
sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep
sp_add_jobstep_internal
sp_delete_job
sp_delete_job_references
sp_delete_jobschedule
sp_delete_jobserver
sp_delete_jobstep
sp_drop_dtspackage
sp_get_dtspackage
sp_update_job
sp_update_jobschedule
sp_update_jobstep
and you assign that role to the (none-sa) user which you want to give the possibilty to start a job, he has only permissions to stop and start a job and cannot change or create dts packages or jobs.

 

[Zukkster]

I have trawled the Internet for that, and everyone just says set up a Proxy account and then...

They never tell you how to set up the Proxy account.

That's a big help. Thanks. A star on it's way

 

[SFreilinger]

I have a similar situation but I have SQL 7.0 (currently doing development with MSDE).

I have unchecked "Only users with sysadmin..." under Non-SysAdmin job step proxy account, but I do not get a box that indicates username and password.

Details of my situation: I have saved a dts package as a COM Structured File, and have created a batch file to use dtsrun to run the package. It works on my MSDE installation, but our DBA's obviously do not want just anyone to have SysAdmin rights to run the package. The package also accesses text files on another server so the user who runs the package needs NT permissions to get to the server.

Any help would be appreciated.
-Shawn