Granting Windows AD group to access to Integration Services Catalog in SQL Server 2012

Issues

Though we give access to AD group in SSIS Package folder directly, the AD group members are not able to see SSIS packages under Integration Service Catalogs.  I did a little bit of research and found that Microsoft has a bug in Integration Service Catalogs security. The issues will exist if you do not upgrade SQL server 2012 to SP1. Since we are not upgrading SQL server to SP1, we follow workaround steps from Microsoft.

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

First, add AD Group group login in Security on Instance level

USE master
GO

CREATE LOGIN domain\username FROM WINDOWS WITH DEFAULT_DATABASE=master
GO

USE SSISDB
GO

CREATE USER domain\username FOR LOGIN domain\username
GO

Create SSIS_users role in SSISDB

use SSISDB

Go

create role ssis_users

go

Add Domain login to ssis_users

USE SSISDB

GO

ALTER ROLE ssis_users ADD MEMBER domain\username

After you run all the scripts above,

• Right click on your SSIS package folder under Integration Services Catalogs

• Click on Permissions and browse and check ssis_users role. It will be added under logins or roles tab

• Then, give appropriate access to ssis_users under Explicit tab. That's it.  Now, members of AD groups can view SSIS packages.