Hiding DTS Packages Names

Would you like to know how to hide Data Transformation Services (DTS) packages from a certain set of Enterprise Manager users?

Brian Moran

August 18, 2003

1 Min Read
ITPro Today logo in a gray background | ITPro Today

I'm trying to make my SQL Server installation as secure as possible. Some of my users need access to Enterprise Manager, but I don't want them to view Data Transformation Services (DTS) packages on the server. Enterprise Manager lets me restrict who can run or edit a package, but all users can still see the package names. Can I hide DTS packages from a certain set of users?

Enterprise Manager uses an undocumented stored procedure in msdb called sp_enum_dtspackages to retrieve package information for display in the GUI. If you remove (or deny) a particular user's EXECUTE permission for this procedure, Enterprise Manager won't show package names in the user's GUI. The user won't see an error message—the server will simply look like it doesn't have any defined packages. By default, SQL Server users don't have permission to query msdb's base tables, which store package information, so your users can't retrieve package names that way, either.

In general, you shouldn't modify undocumented system objects, even at a permission level, because Microsoft doesn't guarantee that it won't change the way something works in a service pack or hotfix. But as far as I know, changing the EXECUTE permissions for sp_enum_dtspackages won't cause any harm and will solve your problem.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like