Home Dashboard Directory Help
Search

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: 

Closed
 as Fixed Help for as Fixed


28
0
Sign in
to vote
Type: Bug
ID: 765980
Opened: 10/3/2012 1:44:01 PM
Access Restriction: Public
2
Workaround(s)
view
13
User(s) can reproduce this bug

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!
Details
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
Sign in to post a workaround.
Posted by Matija Lah on 3/20/2013 at 8:53 AM
I've discussed the workaround proposed below in the following blog post:
http://milambda.blogspot.com/2013/03/ssis-2012-bug-with-windows-group.html
Posted by Matija Lah on 11/16/2012 at 2:34 AM
To work around this problem, use a database role:

1) Create a new database role (e.g. named ssis_user) in the SSISDB database.
2) Add the login L, belonging to the Windows NT group, to the ssis_user SSISDB database role.
3) Assign the appropriate permissions to the ssis_user SSISDB database role.

While this workaround solves the problem, it is far from convenient.