SSIS 2012 Bug with Windows Group Permissions

In October 2012 a bug was discovered in SQL Server 2012 Integration Services, specifically in the SSISDB Catalog, where permissions assigned to an SSISDB user mapped to a Windows Group login are not determined correctly at run time.

The SSISDB Catalog security model extends the native SQL Server security model to allow the permissions to be managed at various SSISDB object levels (e.g. folders, projects, packages, etc.). This extension is implemented inside the SSISDB database and is not fully integrated with the SQL Server security model. As a result, the actual SSISDB object permissions are determined per user at run time.

Fellow Microsoft MVP Phil Brammer (@PhilBrammer) pinpointed the source of the problem to an SSISDB catalog view, which he proposed be changed accordingly. As you can observe in the Connect item, the solution is very simple, but I am sure you can agree that implementing it would improve the usability of the SSISDB Catalog quite significantly. The item is still active, and if you are already using the new SSIS project deployment model, or are planning to use it at any time in the future, I urge you to vote on Connect for the problem to be corrected.

Is There a Workaround?

Until Microsoft resolves the issue, you can work around it by using the following approach, which I also described on Connect:

1. Create a new database role (for instance, named ssis_user) in the SSISDB database.
2. Add the login, based on the Windows NT group that you want to assign the permissions to, to this newly created database role.
3. Assign the appropriate permissions to the ssis_user SSISDB database role by using the SSISDB DCL procedures.

For instance, you can use the following SSMS Transact-SQL template to create the database role:

use SSISDB;
go

create role ssis_user
 authorization dbo;

alter role ssis_user
 add member <database_principal, sysname, Database principal>;
go

In SSMS, use the Ctrl + Shift + M keyboard shortcut to complete the script. You can find more information about SSMS Transact-SQL Templates in SQL Server Books Online.

ML