Integration Services catalog permissions don't work for Windows groups in SQL Server 2012 or SQL Server 2014

Applies to: SQL Server 2012 Business IntelligenceSQL Server 2012 DeveloperSQL Server 2012 Enterprise More

Symptoms


Consider the following scenario:
  • You run an instance of Integration Services catalog and SSISDB catalog in Microsoft SQL Server 2012 or SQL Server 2014.
  • You grant permissions to the members of the Windows group so that they have access to an SSIS catalog folder, project, or environment securable item.

    Note You can grant the permissions in any of the following ways:
    • You use Management Studio. To do this, right-click the object in the Integration Services catalog, select Properties, and then, on the Permissions page, browse to the group, add the group, and then select the permissions for that group.
    • You call the following stored procedure:
      [SSISDB].[catalog].[grant_permission]
  • A Windows user who is a member of the Windows group tries to view and execute the packages in the SSISDB database in the Integration Services catalog by using Management Studio or the stored procedure.

In this scenario, the user cannot see the packages to which they were granted permissions and cannot validate or run those packages.

Cause


This problem occurs because of an issue in SSISDB internal views. This prevents the user from having the expected effective permissions when the permissions are granted by using Windows Group membership.

Resolution


The issue was first fixed in the following cumulative update of SQL Server.

Cumulative Update 1 for SQL Server 2014

Cumulative Update 7 for SQL Server 2012 SP1

Workaround


To work around this problem, don't use the Windows group of various users to access the SSIS securable. Instead, use a user-defined database role in SQL Server to encapsulate the necessary list of Windows users, and then assign the permissions to that database role.

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

References


For more information, see the following topics on the Microsoft Developer Network (MSDN) website:


See the terminology that Microsoft uses to describe software updates.