Permission Denied on sysdtspackages

[fuzzyocelot]

I have a user who needs to be able to edit and run DTS packages on SQL 2005. We will migrate the DTS packages to Integration Services down the road. In the meantime, he needs to be able to manually run at least one particular package. He downloaded and installed the 2000 DTS Components for SQL2005. Now he's getting a permissions error and can't even see the packages. The error is below.

The SELECT permission was denied on the object 'sysdtspackages', database 'msdb', schema 'dbo'. (Microsoft SQL Server, Error: 229)

Do I need to give him explicit permissions to this table? Or is there something else I need to do?

Thanks!

 

[ptheriault]

In 2005 your can add them to the database role db_dtsadmin, db_dtstduder or db_dtsoperator in the msdb database.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

I'll give it a shot! Thanks!

 

[fuzzyocelot]

I tried it out and it didn't work unless I did something wrong. I created a "testuser" login, created a "testuser" user in the msdb database, assigned it to the login, and assigned the "testuser" user in the msdb db the db_dtsadmin role. I then logged into Management Studio as the "testuser" and got the same error message when I tried to open the Legacy folder for the DTS packages.

use master
go
create login testuser with password = 'xxxx'
go
use msdb
go
create user testuser for login testuser
go
sp_addrolemember db_dtsadmin, testuser
go

According to the BOL for 2005, the db_dtsadmin should have given the user the rights to at least see all the packages even if it's not the owner. Any ideas?

Thanks!

 

[ptheriault]

Have you tried to run, open or edit the DTS packages as 'sa'?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

I am a sysadmin on this server so I have no problems in opening or running these DTS packages. I have a user who needs to be able to open, edit, and run these packages. I was hoping I'd be able to figure this out without having to have him do it as the 'sa'. He is the owner of the server and has the 'sa' login, but he's more comfortable using his normal login which gives him only read/write permissions on certain databases. I suppose I could tell him to just log in as the 'sa' when he wants to run those DTS packages.

 

[fuzzyocelot]

I supposed I could just explicitly grant him the select permission on the sysdtspackages table in msdb. Is this a good idea or a bad one?

Thanks!

 

[ptheriault]

You could try that, but the roles should have worked. I use it here for the developers.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[fuzzyocelot]

Maybe for now I'll explicitly grant him the select permission on the sysdtspackages. I'll continue to investigate why the roles you mentioned didn't work. Then again, I'm trying this out on our development server firstwhich has multiple instances (2 for 2000 and 1 for 2005). I'm positive I tried this on the 2005 instance. I could try assigning him to the db_dtsadmin role on the production server and see if it works for him. I just thought I'd try it out on our development server first.