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?