Viewing legacy DTS packages in SSMS is useful. There are still many DTS packages in SQL Server environments , for different reasons DBAs decide not to convert in SSIS packages.
I installed SQL Server Management Studio , to view a SQL Server 2000 instance .
I attempted to access the packages via the : "Management | Legacy | Data Tramsformation Services"
and got the following message:
SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo'
I was perplexed , because when I attempted to view the DTS packages via SQL Server 2000 Enterprise Manager - I could view the DTS details.
The workaround for the problem was to :
1) creating a new role called "DTS_user"
2) assigned EXECUTE on sp_enum_dtspackages and sp_get_dtspackage in msdb.
3) Assign the role to the user
