Restrict Create DTS package rights

[franckch]

I would like to find a way to restrict the number
of people that are able to create DTS package.

I have heard this could be manage with some
grant with store procedure.

Many thanks for help.

 

[JamesLean]

You can't set "create package" rights so to speak. When you save a package you can set a user password, which allows you to just run the package, and an owner password which allows you to edit the package.

Also, don't install client tools (eg Enterprise Manager) for people that don't need them.

Remember, even if a user creates a package, they can only execute queries that they have permissions for anyway. So if they can't select from a certain table thru Query Analyzer then they won't be able to read from it thru DTS.

--James

 

[Corran007]

actually you can james.

if you deny access to the sp_add_dtpackage procedure you will deny it.

i would create a role for dtsusers.
The following procedures need to be changed. you first revoke from public, then grant to the dts role

these are some.
sp_enum_dts
sp_get_dts
sp_add_dtspackage
sp_make_dtspackagename
sp_drop_dtspackage
sp_make_dtspackagename

there a bunch of them. let me go through and write them up, then ill reply when its done

 

[Corran007]

ok here are the scripts. first locks it down, second gives it to dtsuser role

Use MSDB
Go
Print 'Revoking Access for DTS Create and Read'

Revoke Execute on msdb..sp_enum_dtspackages to public
Revoke Execute on msdb..sp_enum_dtspackagelog to public
Revoke Execute on msdb..sp_enum_dtssteplog to public
Revoke Execute on msdb..sp_enum_dtstasklog to public
Revoke Execute on msdb..sp_get_dtspackage to public
Revoke Execute on msdb..sp_get_dtsversion to public

Revoke Execute on sp_add_dtspackage to public
Revoke Execute on sp_drop_dtspackage to public
Revoke Execute on sp_make_dtspackagename to public
Revoke Execute on sp_drop_dtspackage to public
Revoke Execute on sp_get_dtspackage to public

Revoke Execute on sp_reassign_dtspackageowner to public
Revoke Execute on sp_reassign_dtspackagecategory to public

Revoke Execute on sp_add_dtsCategory to public
Revoke Execute on sp_drop_dtscategory to public
Revoke Execute on sp_modify_dtscategory to public
Revoke Execute on sp_enum_dtscategories to public

Revoke Execute on sp_log_dtspackage_begin to public
Revoke Execute on sp_log_dtspackage_end to public
Revoke Execute on sp_log_dtsstep_begin to public
Revoke Execute on sp_log_dtsstep_end to public
Revoke Execute on sp_log_dtstask to public

Revoke Execute on sp_dump_dtslog_all to public
Revoke Execute on sp_dump_dtspackagelog to public
Revoke Execute on sp_dump_dtssteplog to public
Revoke Execute on sp_dump_dtstasklog to public




Use MSDB
Go

If (Select count(*) from sysusers where name = 'DTSUsers') = 0
Begin
Exec Sp_addrole 'DtsUsers'
Print 'Create DtsUsers Role'
End

Print 'Granting Access for DTS Create and Read'

GRANT EXECUTE on msdb..sp_enum_dtspackages to DTSUsers
GRANT EXECUTE on msdb..sp_enum_dtspackagelog to DTSUsers
GRANT EXECUTE on msdb..sp_enum_dtssteplog to DTSUsers
GRANT EXECUTE on msdb..sp_enum_dtstasklog to DTSUsers
GRANT EXECUTE on msdb..sp_get_dtspackage to DTSUsers
GRANT EXECUTE on msdb..sp_get_dtsversion to DTSUsers

GRANT EXECUTE on sp_add_dtspackage to DTSUsers
GRANT EXECUTE on sp_drop_dtspackage to DTSUsers
GRANT EXECUTE on sp_make_dtspackagename to DTSUsers
GRANT EXECUTE on sp_drop_dtspackage to DTSUsers
GRANT EXECUTE on sp_get_dtspackage to DTSUsers

GRANT EXECUTE on sp_reassign_dtspackageowner to DTSUsers
GRANT EXECUTE on sp_reassign_dtspackagecategory to DTSUsers

GRANT EXECUTE on sp_add_dtsCategory to DTSUsers
GRANT EXECUTE on sp_drop_dtscategory to DTSUsers
GRANT EXECUTE on sp_modify_dtscategory to DTSUsers
GRANT EXECUTE on sp_enum_dtscategories to DTSUsers

GRANT EXECUTE on sp_log_dtspackage_begin to DTSUsers
GRANT EXECUTE on sp_log_dtspackage_end to DTSUsers
GRANT EXECUTE on sp_log_dtsstep_begin to DTSUsers
GRANT EXECUTE on sp_log_dtsstep_end to DTSUsers
GRANT EXECUTE on sp_log_dtstask to DTSUsers

GRANT EXECUTE on sp_dump_dtslog_all to DTSUsers
GRANT EXECUTE on sp_dump_dtspackagelog to DTSUsers
GRANT EXECUTE on sp_dump_dtssteplog to DTSUsers
GRANT EXECUTE on sp_dump_dtstasklog to DTSUsers

These arnt entrily mine, borrowed and corrected from brian knight and