April 3, 2009 at 12:11 pm
Currently I need to give access to a user that is having an issue with his DTS package. He gets the error The select permission was denied on the object 'sysdtspackages', database 'msdb', schema 'dbo' . I tried granting select access to this object but it doesn't work. How do I grant access to this? Do I do it through the db_dtsoperator?
April 7, 2009 at 3:13 pm
Hi bruce,
Despite it's name, db_dtsoperator (and the other dts roles in msdb) only works for SSIS packages. The best way I've found is to grant EXECUTE permission to some stored procedures in msdb. See my post in this thread for the recommended stored procedures: http://www.sqlservercentral.com/Forums/FindPost451364.aspx. Note that you might not need to grant permission to all the listed procedures. It depends on what you want users to be able to do.
Greg
September 15, 2009 at 10:47 am
Sorry to jump in. I believe this is exactly waht iam looking for. I am the Systems Analyst on a project stuck between the developer and the very junior DBA. The developer only requested "xxx" after searching this site, I believe this is exactly what I need to give the DBA. HOWEVER, the link to the list appears to be broken. Can you possibly repost or resend
Thanks,
Ray
September 18, 2009 at 10:13 am
The link works now. Must have been a transient problem.
In any case, here are the stored procedures:
sp_enum_dtspackagelog
sp_enum_dtspackages
sp_enum_dtssteplog
sp_enum_dtstasklog
sp_get_dtspackage
sp_get_dtsversion
sp_drop_dtspackage
sp_add_dtspackage
Greg
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply