Permission to a user to run a dts package

Question

Permission to a user to run a dts package

kalyan_tkc

SSC Veteran

Points: 285
More actions
May 6, 2008 at 11:08 am

#207112

Hi
I need to give permission to a user to just run a dts package(He should not be able to do anything else).
Can somebody tell me whats the best way/ point me to some online resources..
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

SanjayAttray SSChampion Points: 13157 More actions · Answer 1

kalyan_tkc (5/6/2008)
Hi
I need to give permission to a user to just run a dts package(He should not be able to do anything else).
Can somebody tell me whats the best way/ point me to some online resources..
Thanks in advance

Create a job for that DTS package and then add the SQL Server login/user id to TargetServersRole in msdb and grant Execute permission against sp_start_job to TargetServersRole.

SQL DBA.

SanjayAttray SSChampion Points: 13157 More actions · Answer 2

Or try this. Add a role something like DTS_RunGroup to msdb database and grant the following permissions to the role:

GRANT EXECUTE ON [dbo].[sp_get_dtsversion] TO [DTS_RunGroup]

GO

GRANT EXECUTE ON [dbo].[sp_add_dtspackage] TO [DTS_RunGroup]

GO

GRANT EXECUTE ON [dbo].[sp_get_dtspackage] TO [DTS_RunGroup]

GO

GRANT EXECUTE ON [dbo].[sp_enum_dtspackages] TO [DTS_RunGroup]

GO

SQL DBA.

VSubramanian2 Mr or Mrs. 500 Points: 555 More actions · Answer 3

Your script will be useful if a user wants to execute all the dts packages. But i require a user to execute only specific package. what is the query for it.

1 . One user should have execute permission on dts_daily(dts package_name)

2. One user should have read/write access to dts_daily.

can u provide solution for the above