permissions required to access Legacy DTS packages in SQL 2005

Question

permissions required to access Legacy DTS packages in SQL 2005

Jez Galaun

SSC Veteran

Points: 217
More actions
January 26, 2007 at 4:40 am

#174581

Hi-
I carried out an upgrade from sql 2000 to 2005 and all of my dts packages
now appear under the Legacy node under Management. I'm trying to give some
users access to manage these packages and have given the windows login they
are using access to msdb and the db_dtsadmin and public roles.
However when they try to expand the Data Transformation Services node under
Legacy they get the following error:
SELECT permission denied on object 'sysdtspackages', database 'msdb', schema
'dbo'. (Microsoft SQL Server, Error: 229).
Is adding them to the db_dtsadmin role under msdb not sufficient, is there
something I have missed.
Thanks,
Jez.
January 29, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

i don't think you should be assigning logins to the msdb database. you could assign their usernames to one of the server roles.

Everything you can imagine is real.

maleitzel SSC-Addicted Points: 464 More actions · Answer 2

Has anyone resolved this issue? I cannot get any user logins to access legacy DTS packages in SQL Server 2005 without specific permissions at a very high level.

Any help would be appreciated!

Axel Araujo SSC Journeyman Points: 80 More actions · Answer 3

I had the same problem. I resolved it by granting the user access to the MSDB database and Select permission to the SysDTSpackages table.

Hope this will help you.

Axel

maleitzel SSC-Addicted Points: 464 More actions · Answer 4

I ended up creating a new role in msdb and granting that role permissions needed to access the DTS packages. I then added our developers to this role. This was all done in our development environment.

These seems to be working great!

Thanks for the response Axel.

bourgoin SSC Enthusiast Points: 145 More actions · Answer 5

maleitzel,

Could you be more specific on what permissions you granted on that role? Similarly, we've got user now using Managenet Studio with the DTS Designer plugin and are trying to get to DTS packages on SQL 2000 servers.

Thanks,

maleitzel SSC-Addicted Points: 464 More actions · Answer 6

bourgoin

Two things. You should migrate your existing DTS packages to the new SQL Server 2005 instance. This is relatively painless.

Then, create a new role in the MSDB database. Give this role delete, insert, select and update permissions to the sysdtspackages table in the database. Then add your developers to this role.

Your application developers should have access to the DTS packages through Management Studio.

maleitzel SSC-Addicted Points: 464 More actions · Answer 7

PS:

I should have included a step to have these rewritten to SQL Server Integration Services packages as soon as they can be.

bourgoin SSC Enthusiast Points: 145 More actions · Answer 8

Actually, the DTS packages are still sitting on a SQL 2000 server, only the client is at 2k5 and using Mgmt Studio.

Getting them migrated to 2k5/SSIS will eventually happen, however the developers still need to manage the lagacy DTS packages.

maleitzel SSC-Addicted Points: 464 More actions · Answer 9

What error message do you get when you register your 2000 instance in Management Studio and then drill into legacy and try to open a DTS package?

You may need to follow the steps here:

http://support.microsoft.com/kb/917406

Thanks...

bourgoin SSC Enthusiast Points: 145 More actions · Answer 10

The error is:

SELECT permission denied on table sysdtspackages, database 'master', owner 'dbo'

The can manage the DTS packages (on the 2000 sever) from EM just fine. Not sure why we would now get permissions issues from Mgmt Studio.

I rather not start adding permissions directly to users (or realms in our case) to sysdtspackages. It opens up the possibililty of the table being used directly instead of through the built in stored procs.

Greg Charles SSC-Forever Points: 45403 More actions · Answer 11

Rather than granting SELECT on sysdtspackages, you could try granting EXECUTE on sp_enum_dtspackages and sp_get_dtspackage in msdb.

Greg

Wayne West SSC-Insane Points: 22586 More actions · Answer 12

Thank you for the solution, maleitzel. I just started migrating to 2005 and hit this problem this morning with one of my developers. I didn't notice it as I ran as DBA.

-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]