Search code examples
sqlsql-serversql-server-2016

Find a list of users with the same set of permission groups

We are adding Roles to our system for our security setup.

We are trying take our main UserItems table to find a list of users with the same set of permission groups so that we can suggest to the end user what roles should be setup and with what permission groups.

Here is the table setup:

    CREATE TABLE [dbo].[UserItems](
    [UserItemId] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] NOT NULL,
    [PermissionGroupId] [int] NULL,
    [CanModify] [bit] NOT NULL,
    [ItemOrganizationId] [int] NOT NULL,
    [SortOrder] [int] NOT NULL,
 CONSTRAINT [PK_UserItem] PRIMARY KEY CLUSTERED 
(
    [UserItemId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I've tried several SQL statements, I'm having no luck.

I'm trying to obtain a list of users with the same set of permission groups, so we can determine possible roles based on what the customer has set up for the UserItems.

SELECT        COUNT(*) AS TotalUsers, dbo.UserItems.PermissionGroupId,      dbo.PermissionGroups.PermissionGroupName
     FROM            dbo.UserItems INNER JOIN
                         dbo.PermissionGroups ON dbo.UserItems.PermissionGroupId =    dbo.PermissionGroups.PermissionGroupId
    GROUP BY  dbo.UserItems.PermissionGroupId,  dbo.PermissionGroups.PermissionGroupName
    HAVING        (COUNT(*) > 1)
    ORDER BY TotalUsers DESC


    SELECT        COUNT(*) AS TotalUsers,  dbo.UserEndItems.PermissionGroupId
FROM            dbo.UserItems
    Group by dbo.UserItems.PermissionGroupId
    HAVING        (COUNT(*) > 1)
    ORDER BY TotalUsers DESC

How can I obtain a list of users with the same set of permissions groups so that we can make roles based on that data?

UserItemId UserId PermissionGroupId CanModify ItemOrganizationId SortOrder
1 228 420 1 5001 4
2 228 426 0 4395 5
3 228 426 0 5152 5
4 228 426 0 5154 5
5 228 426 0 4861 5
6 228 426 0 4394 5
7 228 426 0 4442 0
8 228 426 0 5102 5
9 228 426 0 5100 5
10 228 419 0 4145 5
11 228 420 0 4102 0
12 228 423 0 4112 0
13 228 419 0 4098 0
14 228 426 0 4224 0
15 228 426 0 5785 3
16 228 499 0 6617 0
17 228 499 0 6619 0
18 228 499 0 6621 0
19 228 499 0 6623 0
20 107 499 1 6617 0
21 107 499 0 6619 0
22 107 499 0 6621 0
23 107 499 0 6623 0
24 228 426 0 6970 0
25 108 426 0 4395 0
26 108 426 0 5152 0
27 108 426 0 5154 0
28 108 426 0 4861 0
29 108 426 0 4394 0
30 108 426 0 4442 0
31 108 426 0 5102 0
32 108 426 0 5100 0
33 108 426 0 4224 0
34 108 426 0 5785 0
35 108 426 0 6970 0
36 137 426 0 4395 0
37 137 426 0 5152 0
38 137 426 0 5154 0
39 137 426 0 4861 0
40 137 426 0 4394 0
41 137 426 0 4442 0
42 137 426 0 5102 0
43 137 426 0 5100 0
44 137 426 0 4224 0
45 137 426 0 5785 0
46 137 426 0 6970 0
47 107 426 0 4395 0
48 107 426 0 5152 0
49 107 426 0 5154 0
50 107 426 0 4861 0
51 107 426 0 4394 0
52 107 426 0 4442 0
53 107 426 0 5102 0
54 107 426 0 5100 0
55 107 426 0 4224 0
56 107 426 0 5785 0
57 107 426 0 6970 0
58 235 426 0 4395 0
59 235 426 0 5152 0
60 235 426 0 5154 0
61 235 426 0 4861 0
62 235 426 0 4394 0
63 235 426 0 4442 0
64 235 426 0 5102 0
65 235 426 0 5100 0
66 235 426 0 4224 0
67 235 426 0 5785 0
68 235 426 0 6970 0
69 228 442 0 5738 0
70 228 442 0 5741 0
71 107 531 0 7111 0
72 107 531 0 7113 0
73 107 531 0 7115 0
74 107 531 0 7117 0
75 222 426 0 6970 0
76 222 531 0 7111 0
77 222 531 0 7113 0
78 222 531 0 7115 0
79 222 531 0 7117 0
80 222 499 0 6617 0
81 222 499 0 6619 0
82 222 426 0 5785 0
83 222 426 0 4394 0
84 222 426 0 4395 0
85 222 426 0 4442 0
86 222 426 0 4861 0
87 222 426 0 5100 0
88 222 426 0 5102 0
89 222 426 0 5152 0
90 222 426 0 5154 0
91 222 499 0 6623 0
92 222 499 0 6621 0
93 222 426 0 4224 0
94 228 421 0 4983 0
95 228 421 0 4225 0
96 228 421 0 4142 0
97 228 421 0 4285 0
98 228 421 0 4144 0
99 228 421 0 4141 0
100 228 421 0 4099 0
101 228 421 1 5973 0
102 228 424 0 4112 0
103 228 424 0 4102 0
104 228 424 0 4095 0
105 228 424 0 4101 0
106 228 424 0 4099 0
107 228 424 0 4096 0
108 228 424 0 4098 0
109 228 424 0 4224 0
110 228 424 0 4579 0
111 228 424 0 4586 0
112 228 418 0 4579 0
113 228 418 0 5443 0
114 107 499 0 5784 0
115 222 499 0 5784 0
116 197 418 0 4579 0
117 197 418 0 5443 0
118 197 419 0 4145 0
119 197 419 0 4098 0
120 197 420 0 5001 0
121 197 420 0 4102 0
122 197 421 0 5973 0
123 197 421 0 4141 0
124 197 421 0 4983 0
125 197 421 0 4099 0
126 197 421 0 4142 0
127 197 421 0 4144 0
128 197 421 0 4225 0
129 197 421 0 4285 0
130 197 423 0 4112 0
131 197 424 0 4586 0
132 197 424 0 4579 0
133 197 424 0 4112 0
134 197 424 0 4224 0
135 197 424 0 4099 0
136 197 424 0 4098 0
137 197 424 0 4135 0
138 197 424 0 4095 0
139 197 424 0 4096 0
140 197 424 0 4106 0
141 197 424 0 4102 0
142 197 424 0 4100 0
143 197 424 0 4101 0
144 197 426 0 5102 0
145 197 426 0 5152 0
146 197 426 0 5154 0
147 197 426 0 5100 0
148 197 426 0 4442 0
149 197 426 0 4394 0
150 197 426 0 4395 0
151 197 426 0 5785 0
152 197 426 0 4861 0
153 197 426 0 4224 0
154 197 426 0 6970 0
155 197 442 0 5741 0
156 197 442 0 5738 0
157 197 499 0 6623 0
158 197 499 0 6621 0
159 197 499 0 6617 0
160 197 499 0 6619 0
161 197 499 0 5784 0
162 197 531 0 7111 0
163 197 531 0 7113 0
164 197 531 0 7115 0
165 197 531 0 7117 0
166 197 419 0 6293 0
167 228 419 1 6293 0
168 277 424 0 4135 0
169 277 424 0 4112 0
170 277 424 0 4102 0
171 277 424 0 4095 0
172 277 424 0 4101 0
173 277 424 0 4106 0
174 277 424 0 4099 0
175 277 424 0 4096 0
176 277 424 0 4100 0
177 277 424 0 4098 0
178 277 424 0 4224 0
179 277 424 0 4579 0
180 277 424 0 4586 0
181 198 418 0 4579 0
182 198 418 0 5443 0
183 198 419 0 4098 0
184 198 419 0 4145 0
185 198 419 0 6293 0
186 198 420 0 5001 0
187 198 420 0 4102 0
188 198 421 0 4285 0
189 198 421 0 4141 0
190 198 421 0 4142 0
191 198 421 0 4144 0
192 198 421 0 4225 0
193 198 421 0 5973 0
194 198 421 0 4099 0
195 198 421 0 4983 0
196 198 423 0 4112 0
197 198 424 0 4106 0
198 198 424 0 4135 0
199 198 424 0 4099 0
200 198 424 0 4095 0
201 198 424 0 4098 0
202 198 424 0 4101 0
203 198 424 0 4224 0
204 198 424 0 4102 0
205 198 424 0 4100 0
206 198 424 0 4586 0
207 198 424 0 4579 0
208 198 424 0 4112 0
209 198 426 0 4394 0
210 198 426 0 4395 0
211 198 426 0 4861 0
212 198 426 0 5154 0
213 198 426 0 5100 0
214 198 426 0 4224 0
215 198 426 0 5152 0
216 198 426 0 5102 0
217 198 426 0 5785 0
218 198 426 0 4442 0
219 198 426 0 6970 0
220 198 442 0 5738 0
221 198 442 0 5741 0
222 198 499 0 6617 0
223 198 499 0 6619 0
224 198 499 0 6621 0
225 198 499 0 6623 0
226 198 499 0 5784 0
227 198 531 0 7117 0
228 198 531 0 7115 0
229 198 531 0 7113 0
230 198 531 0 7111 0
231 235 428 0 4280 0
232 235 428 0 5235 0
233 235 428 0 4442 0
234 235 428 0 6970 0
235 277 421 0 4983 0
236 277 421 0 4225 0
237 277 421 0 4142 0
238 277 421 0 4285 0
239 277 421 0 4144 0
240 277 421 0 4141 0
241 277 421 0 4099 0
242 277 421 0 5973 0
243 288 418 0 4579 0
244 288 418 0 5443 0
245 288 419 0 4098 0
246 288 419 0 4145 0
247 288 419 0 6293 0
248 288 420 0 5001 0
249 288 420 0 4102 0
Solution

  • As suggested by @NickW early in the comments:

    After eliminating duplicate UserId/PermissionGroupId combinations, you can group by UserId and use STRING_AGG() to gather the PermissionGroupId values into an ordered comma separated list. After that, you can then group those results by combined group IDs and aggregate the UserIDs having the same group ID collection.

    ;WITH DistinctUserGroups AS (
    SELECT DISTINCT UEI.UserId, UEI.PermissionGroupId
    FROM UserEndItems UEI
),
UserGroupsCombined AS (
    SELECT
        DUG.UserId,
        COUNT(*) AS NGroups,
        STRING_AGG(DUG.PermissionGroupId, ',')
            WITHIN GROUP(ORDER BY DUG.PermissionGroupId)
            AS GroupIdList
    FROM DistinctUserGroups DUG
    GROUP BY DUG.UserId
)
SELECT
    COUNT(*) AS NUsers,
    STRING_AGG(UGC.UserId, ',')
        WITHIN GROUP(ORDER BY UGC.UserId)
        AS UserIdList,
    UGC.NGroups,
    UGC.GroupIdList
FROM UserGroupsCombined UGC
GROUP BY UGC.NGroups, UGC.GroupIdList
ORDER BY UGC.GroupIdList

    Sample results:

    NUsers UserIdList NGroups GroupIdList
    1 288 3 418,419,420
    1 228 9 418,419,420,421,423,424,426,442,499
    2 197,198 10 418,419,420,421,423,424,426,442,499,531
    1 277 2 421,424
    2 108,137 1 426
    1 235 2 426,428
    2 107,222 3 426,499,531

    See this db<>fiddlw.

    Your sample data did not include the PermissionGroups table so the above just shows IDs. It should be a simple matter to include group names instead. You can also add HAVING conditions to eliminate singletons at both levels.