Configure SQL Server Security to Allow Only Browsing of SSIS Packages

By:   |   Comments (2)   |   Related: > Integration Services Configuration Options


I was recently approached by the DEV team and since they have no access in QA and Prod, they asked if they could be able to just see the package and folder structures through SSMS from their workstations. Since Integration Services connectivity is outside of SQL and controlled through the OS, I was unsure if this was possible since I did not see any "Read Only" properties when researching SSIS permissions.


The solution to this problem was developed on Windows Server 2008 R2 running SQL Server 2008 R2 Enterprise Edition. There are 3 different things that I did to get this to work, including local group permissions, DCOM Config settings, as well as MSDB permissions inside of SQL Server. There is a MSDN article related to this, but I had to more steps than were described in the article to get it working.

The following steps are performed on the remote server and I did this through a RDP session.

1. Add AD Group to "Distributed COM Users" local group

By going to Start --> Computer (Right Click) --> Manage, expand Configuration --> Local Users and Groups --> Groups

Computer Management

Right click "Distributed COM Users" and select "Add to Group" and add the AD group for your development team or whomever you are granting read only access to SSIS for.

Add AD Group to Local Group

2. Set DCOM Config Permissions

Since permissions for stopping/starting the SSIS Service as well as connectivity are maintained in the DCOM Config setting for the service, you have to add that group and set its access as well, followed by restarting the SSIS Service for the changes to take effect. As you will see all the permissions I set are "Remote" permissions, so they can connect from their workstations. Local would be RDP'd or sitting on the server, which surely will not be allowed.

Go to Start --> Administrative Tools --> Component Services OR Start --> Run and type "DCOMDNFG.exe" to bring up the UI.

Expand Component Services --> Computers --> My Computer --> DCOM Config

Component Services

Locate the SSIS Service (for 2008 it is named MsDtsServer100, 2012 is called Microsoft SQL Server Integration Services 11.0) and Right Click --> Properties

MsDtsServer100 Properties

Click Edit in the Launch and Activation Section, add the AD group, and grant it Remote Activation permission and select OK. (As an FYI, Launch is the permission to start/stop the SSIS Service, Activation is the ability to connect to the SSIS Service)

Activation Permisisons

Click Edit in the Access Section, add the AD group, and Allow it Remote Access and select OK.

Access Permisisons

Close the Component Services MMC Plugin and restart the Integration Services Service through SQL Server Configuration Manager. This is required for the changes to take effect. Also, be careful to make sure no packages or jobs are running when you bounce the service, but I am sure everyone already knew that.

What we have done is granted that AD group the ability to connect to the service and after some testing I found this did not satisfy the original requirement, they could connect fine, but could not expand folders and see the underlying packages when they tried to expand the MSDB folder. We have one more step to perform.

Connection Error

3. Set MSDB Permissions

Since SSIS packages are stored in the MSDB database, I did not want to grant EXECUTE to the entire schema so we narrowed down to the minimum permissions needed to able to browse the folders and packages. I ended up putting these permissions into a role and granting that role to the AD group.

There are 3 system stored procedures that needed execute permission granted and they are listed below.

  • sp_ssis_listfolders
  • sp_ssis_listpackages
  • sp_ssis_getfolder
USE msdb

GRANT EXECUTE ON sp_ssis_listfolders TO SSISBrowser GRANT EXECUTE ON sp_ssis_listpackages TO SSISBrowser GRANT EXECUTE ON sp_ssis_getfolder to SSISBrowser
EXEC sp_addrolemember N'SSISBrowser', N'YOURDOMAIN\ADGroupName' GO

At this point, the requesting group was able to connect through SSMS to Integration Services on the remote server, browse folder structures and packages. I also tested and verified they could not export, execute, or rename packages.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Churchwell Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, March 20, 2013 - 3:06:39 PM - TonyC Back To Top (22917)

Thank goodness for SQL Server 2012 and Integration Services Catalogs

Wednesday, March 20, 2013 - 3:39:19 AM - Abhi Back To Top (22889)

Good article Chad, Thanks..

get free sql tips
agree to terms