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.

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:

  1. 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

  2. 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

  3. 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

  4. 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:

    Select Grant or Deny on the desired explicit permissions


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.

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

What affected your experience?

Any additional feedback? (Optional)

Thank you for your feedback!

×