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 /en-us/help/2931693
Cumulative Update 7 for SQL Server 2012 SP1 /en-us/help/2894115
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
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.
To do this, follow these steps:
-
Create the necessary Windows logins to the instance of SQL Server, and then give the user access to the SSISDB database. For example, you can use Management Studio by going to Security, clicking Logins, and then clicking New Login. Or, you can use the following script for each Windows user logon:
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 -
In the SSISDB database, create a user-defined database role, such as ssis_users. For example, you can use Management Studio by going to Databases, clicking SSISDB, clicking Security, clicking Roles, clicking Database Roles, and then clicking New Database Role. Or, you can use the following script:
USE SSISDB
GO
CREATE ROLE ssis_users
GO -
Add the desired users in SSISDB into the role ssis_users. For example, you can use Management Studio by going to Databases, clicking SSISDB, clicking Security, clicking Roles, clicking Database Roles, clicking Role Members, and then clicking Add. Or, you can use the following script:
USE SSISDB
GO
ALTER ROLE ssis_users ADD MEMBER domain\username
GO -
Right-click the securable object in the Integration Services Catalog, click Properties, click Browse on the Permissions page to add the Database Role into the permissions, and then select Grant or Deny on the desired explicit permissions as follows:
Note These permissions may not be sufficient to successfully execute an SSIS package. Each SSIS package is a custom design and may have additional specific requirements to access certain servers and databases or shares to validate or execute the SSIS package successfully.
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:
SSIS Catalog
Folder Properties Dialog Box
Project Properties Dialog Box
Environment Properties Dialog Box
See the terminology that Microsoft uses to describe software updates.
