In versions of SQL Server ealier than SQL Server 2008, the Execute right on some of the DTS system stored procedures is granted to PUBLIC by default. By using this setting, anyone who can log on to the
msdb database can do the following tasks:
- Get a list of DTS packages.
- Retrieve the packages themselves.
- Save new packages.
However, only the owner of a package can modify an existing package by saving a new version of the package, or delete an existing package. (DTS modifies a package by saving a new version of the package. DTS does not overwrite the current version of the package.)
The potential security issue occurs if an application connects to SQL Server by using a login that owns DTS packages. In this scenario, there is the risk that a SQL injection attack could modify or delete existing packages.
For more information and a complete list of affected DTS system stored procedures, please refer to the article Securing DTS Packages Stored in SQL Server. We recommend that you execute the following query in MSDB database to retrieve the list of DTS system stored procedures which have Execute permissions granted to PUBLIC role.
select OBJECT_NAME (major_id),permission_name,state_desc from msdb.sys.database_permissions
where USER_NAME(grantee_principal_id) = 'PUBLIC' and permission_name = 'Execute' and state_desc = 'GRANT' and OBJECT_NAME(major_id) IN ('sp_get_dtsversion','sp_make_dtspackagename', 'sp_add_dtspackage','sp_drop_dtspackage','sp_reassign_dtspackageowner', 'sp_get_dtspackage','sp_enum_dtspackages','sp_log_dtspackage_begin', 'sp_log_dtspackage_end','sp_log_dtsstep_begin','sp_log_dtsstep_end', 'sp_log_dtstask','sp_enum_dtspackagelog','sp_enum_dtssteplog', 'sp_enum_dtstasklog','sp_dump_dtslog_all','sp_dump_dtspackagelog', 'sp_dump_dtssteplog', 'sp_dump_dtstasklog')
You can execute the stored procedure sp_dts_secure with parameter 1 to Revoke Execute permissions on all DTS system stored procedures from PUBLIC role.
For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Rule software | Rule title | Rule description | Product versions against which the rule is evaluated |
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)
| Potential security issue with legacy DTS stored procedures
| The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides rule to detect when an instance of SQL Server contains DTS system stored procedures which have Execute permissions granted to PUBLIC role. If you run the BPA tool and encounter an Error with the title of Engine - Potential security issue with legacy DTS stored procedures, we detected at least one DTS system stored procedure that has Execute permissions granted to PUBLIC role. | SQL Server 2008
SQL Server 2008 R2
|
SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)
| Potential security issue with legacy DTS stored procedures
| The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides rule to detect when an instance of SQL Server contains DTS system stored procedures which have Execute permissions granted to PUBLIC role. If you run the BPA tool and encounter an Error with the title of Engine - Potential security issue with legacy DTS stored procedures, we detected at least one DTS system stored procedure that has Execute permissions granted to PUBLIC role. | SQL Server 2012 |