How to Grant access to a User to view the DTS packages.

Posted on 2011-04-20
Last Modified: 2012-05-11
Hi All,

How to Grant access to a User to view the DTS packages. This access is required to view and modify the DTS packages as part of the issues that are being worked on.

Thanks in Advance.
Question by:msdba
    LVL 2

    Accepted Solution

    db_dtsadmin will allow them to see all the packages, it is the role trhat seems to best fit for 2005. As far as 2000, there is no real easy way, you can use a proxy, but it is not as grainular as you would think.
    LVL 23

    Assisted Solution

    - i would suggest, if you have a development server let the developer view, modify and test in development environment and then the DBA deploy it on the production. it's much secure that way if your system is already in production.

    - nevertheless granting access might work this way:
    : add DTS_Developer role to MSDB
    : grant these permission to the role:
    - sp_add_dtspackage EXECUTE
    - sp_enum_dtspackages EXECUTE
    - sp_get_dtspackage EXECUTE
    - sp_get_dtsversion EXECUTE
    : then add the user/developer to that new created role

    Author Closing Comment


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now