Catalog Permissions Don't Work for Windows Group

According to this KB article (https://support.microsoft.com/en-us/kb/2890052#/en-us/kb/2890052),
the issue with Windows Group and SSIS Catalog was fixed in the CU7 for SQL
Server 2012 SP1. I'm currently running SQL Server 2012 SP2 but am still having
this issue. When the Windows user who is a member of the Windows group tries to
view and execute a SSISDB package, the user cannot see the folder. Is anybody
else having this issue still?<o:p></o:p>


July 21st, 2015 7:41pm

Hi CADBA,

After testing the issue in my SQL Server 2012 SP2 environment, the user can see the packages to which they were granted permissions and can validate or run those packages. It means that the issue is fixed in SQL Server 2012 SP2.

As per my understanding, I think you may not grant permissions to the members of the Windows group so that they have no access to an SSIS catalog folder, project, or environment securable item. For more details, please refer to the following blog:
http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx

So please verify that you SQL Server version is SQL Server 2012 SP2, and also make sure you have granted appropriate permissions to the Windows group.

Thanks,
Katherine Xiong

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 4:54am

Hi Katherine,

Thanks for your response. I did verify that the SQL Server version is SQL Server 2012 SP2 and that I have granted the appropriate permission to the Windows group. The version number on the SQL Server is 11.0.5058. I've granted the windows group ID all the permissions listed on the catalog folder properties and the catalog project properties. If I do assign the windows group ID the ssis_admin role, then yes the windows group ID can see all the catalog folders and is able to run all the packages. However, I don't want the windows group ID to have access to all the folders and packages. Per the steps mentioned in case #1 or #2 in the article, I've verified that the windows group ID does have the public role assigned and is granted Read/Execute/Read Objects permissions. My issue still exists. The windows group ID can't see the one folder and package it has access to. I've also looked at this article https://www.mssqltips.com/sqlservertip/3153/managing-ssis-security-with-database-roles/ and it says I should be able to assign "users appropriate permissions (without just adding them to the ssis_admin role).

Any other ideas? Thanks for your help.

July 23rd, 2015 7:45pm

Hi CADBA,

Please double-check you have granted Read permission for the Windows Group in the folder of SSIS Catalog. For more details, please see:
https://msdn.microsoft.com/en-us/library/ff878150.aspx

Thanks,
Katherine Xiong 

Free Windows Admin Tool Kit Click here and download it now
July 23rd, 2015 10:52pm

Hi Katherine,

I've double checked the setting. Yes, the Windows Group ID does have Read permission to the SSIS Catalog. In the folder properties, in the Permissions page, the Read permission under the Grant column is checked. I also ran this "Exec [SSISB].[catalog].[grant_permission] @object_type=1, @object_id=6, @principal_id=10, @permission_type =1" just to make sure. I also applied the same steps to verify that the Windows Group ID does have Read permission to the project folder. The Windows Group ID still cannot view the folder.

Thanks for your continued assistance.

July 24th, 2015 2:31pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics