SQL 2012 SSISDB catalog - all permission on folder, but still can't see it without ssis_admin (if login is a group) - by Blaž Dakskobler

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


28
0
Sign in
to vote
ID 765980 Comments
Status Closed Workarounds
Type Bug Repros 13
Opened 10/3/2012 1:44:01 PM
Access Restriction Public

Description

My goal with SSISDB catalog's folders is to limit users to only a certain folder, but inside that folder they're essentially admins. I don't want them to have any permissions whatsoever on other folders. Seemed easily doable via folder permissions, right? Well, I didn't manage to achieve that, despite user's login (a group he was a member of) having all permissions on a folder. The only thing that helped was ssis_admin role on SSISDB database, but that is of course not ideal, since then the user has permissions on all folders.
Bug or am I missing something / doing something wrong?

To reproduce:
- Put a user into a Windows group (this might be the problem, a login for a single user worked fine).
- Create a login L for the group.
- Create a user for this login in SSISDB database (public role only).
- Create folder X in SSISDB catalog.
- Give login L all permissions on folder X (read, modify...). Do not make L a member of ssis_admin role on SSISDB database.

User can't see the folder in SSMS GUI (or catalog.folders view), not unless I make his login a member of ssis_admin role on SSISDB database.

As I mentioned above: I tried this repro with a login for single AD user (not a group!) and had no problem there!
Sign in to post a comment.
Posted by Blaž Dakskobler on 11/18/2013 at 11:46 PM
For future reference, the KB article is here: http://support.microsoft.com/kb/2890052
The fix itself is included in Cumulative Update 7 for SQL Server 2012 SP1.
Posted by Microsoft on 10/3/2013 at 12:06 PM
Hello Blaž.
Thanks for bringing this to our attention. This has been fixed. The change will be included soon in a future release or servicing release for SQL Server.
-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Microsoft on 9/11/2013 at 9:56 AM
Hello Blaž.
Thank you for bringing this to our attention. We really do appreciate the feedback. You can probably tell by the rate of upvotes that we're taking this one pretty seriously. Needless to say, we're on it.
-Walter A Jokiel, Program Manager, SQL Server (wajokiel@microsoft.com)
Posted by Phil Brammer on 11/16/2012 at 9:48 AM
I can confirm this behavior in 2012 SP1 (11.0.3000). The view should be:

CREATE VIEW [internal].[current_user_object_permissions]
AS
SELECT     [object_type],
         [object_id],
         [permission_type],
         [sid],
         [is_role],
         [is_deny]
FROM     [internal].[object_permissions]
WHERE     IS_MEMBER(USER_NAME([internal].[get_principal_id_by_sid]([sid])))=1
OR     [sid] = USER_SID (DATABASE_PRINCIPAL_ID())

/* Original, Microsoft code:
SELECT     [object_type],
         [object_id],
         [permission_type],
         [sid],
         [is_role],
         [is_deny]
FROM     [internal].[object_permissions]
WHERE     ([is_role] = 0 AND [sid] = USER_SID (DATABASE_PRINCIPAL_ID()))
         OR ([is_role] =1 AND IS_MEMBER(USER_NAME([internal].[get_principal_id_by_sid]([sid])))=1)
*/
GO