July 10, 2001 at 12:46 am
Hi all
I read a while back (somewhere!) about what msdb stored procedures to restrict to prevent users from
a) Creating or modifying packages
b) Viewing (seeing a list of) packages)
I can take some pot guess and spend hours doing it, but can any point out the exec privs for some of the sp_enum procedures in msdb that you would recommend to set.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 10, 2001 at 11:01 am
One thing that is easy is to set a password for the packages. I set one on all my packages.
The packages are stored in msdb.dbo.sysdtspackages, so you can set permissions on this table.
Steve Jones
July 10, 2001 at 7:35 pm
Steve
Thanks Steve. I will try it. The password option is fine, but doesnt stop people creating new packages all over the place. I have tried restricting access to:
sp_add_dtspackage
sp_add_job
sp_enum_dtspackages
with mixed results to date... I will look at other procedures later.
Cheers
Chris
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
July 10, 2001 at 10:13 pm
Easiest way would be to run Profiler while you create the package, that should give you the list without too much work!
Andy
July 10, 2001 at 10:23 pm
You may have read it from this tip:
http://www.sqlservercentral.com/columnists/bknight/dtslockdown.asp
You're right on with the stored procedures you need to deny. Of course, simply placing owner and user passwords will take care of your modifying and viewing packages. It will not take of your creating requirement you mentioned in point A.
Brian Knight
Brian Knight
Free SQL Server Training Webinars
July 11, 2001 at 10:52 pm
Thanks guys...
yep, profiler came in handy (cant believe I forgot about it!)..
to add new:
sp_add_dtspackage
sp_get_dtsversion
to get a list of:
sp_enum_dtspackages
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 21, 2005 at 2:24 am
Try checking out this web page gives full details of risks of allowing people to build dts packages. https://www.appsecinc.com/Policy/PolicyCheck2644.html
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply