InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 23:18:36 GMT20http://www.sqlservercentral.com/Forums/Topic1372392-392-1.aspx[quote][b]whenriksen (10/16/2012)[/b][hr]@dwain.cExcellent, that is just what I needed. After working with it for a bit this morning, I've determined I need to assign the groups string in a checksum rather than a row_number. Now I don't need to worry about row_number assigments changing erratically when new group combinations are used. The checksum values won't be affected by permissions changes.Thanks again.Wes[/quote]Happy to hear that my suggestion seems to have gotten you on the right track.Tue, 16 Oct 2012 18:38:08 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1372392-392-1.aspx@dwain.cExcellent, that is just what I needed. After working with it for a bit this morning, I've determined I need to assign the groups string in a checksum rather than a row_number. Now I don't need to worry about row_number assigments changing erratically when new group combinations are used. The checksum values won't be affected by permissions changes.Thanks again.WesTue, 16 Oct 2012 10:06:17 GMTwhenriksenhttp://www.sqlservercentral.com/Forums/Topic1372392-392-1.aspxNo need to use dynamic SQL or a PIVOT for this.[code="sql"];WITH GroupsGrouped AS ( SELECT DISTINCT Groups=STUFF(( SELECT ',' + CAST(GroupID AS VARCHAR(5)) FROM PersonGroups b WHERE a.PersonID = b.PersonID ORDER BY GroupID FOR XML PATH('')), 1, 1, '') FROM personGroups a GROUP BY PersonID)SELECT RoleID, GroupNameFROM ( SELECT RoleID=ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) ,Groups FROM GroupsGrouped) a CROSS APPLY dbo.DelimitedSplit8K(Groups, ',') bINNER JOIN Groups c ON c.GroupID = b.Item[/code]The trick is to create a delimited string (I chose comma for my delimiter) of each combination of groups for a user. You can then apply a ROW_NUMBER() to get the RoleID and unravel the whole thing using a delimited string splitter (DelimitedSplit8K), like the one popularized here by Jeff Moden: [url]http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]Sun, 14 Oct 2012 18:44:23 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1372392-392-1.aspxIf dynamic pivot will solve your case, here it is: [url=http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/]http://sqlwithmanoj.wordpress.com/2011/01/25/dynamic-pivot/[/url]But I think you should rethink your concept.[code="sql"]-- users with object-level permissions in current dbsp_helprotect NULL, NULL, NULL, 'o'-- users with server-level permissions in current dbsp_helprotect NULL, NULL, NULL, 's'-- Object-level permissions for all databasesIF OBJECT_ID('tempdb..#p','U') is not null DROP TABLE #Pcreate table #p( DBname nvarchar(500), Owner sysname, Object sysname, Grantee sysname, Grantor sysname, ProtectType varchar(100), Action varchar(100), [Column] varchar(100))EXEC sp_msforeachdb 'INSERT INTO #p(Owner, Object, Grantee, Grantor, ProtectType, Action, [Column]) exec ?.dbo.sp_helprotect NULL, NULL, NULL, ''o''UPDATE #p SET DBName=''?'' WHERE DBName IS NULL'select * from #p-- Roles grantedIF OBJECT_ID('tempdb..#g','U') is not null DROP TABLE #GCREATE TABLE #g( DBName varchar(100), UserName nvarchar(500), GroupName nvarchar(500), LoginName nvarchar(500), DefDBName nvarchar(500), DefSchemaName nvarchar(500), UserID int, SID image)exec sp_msforeachdb'insert into #g(UserName, GroupName, LoginName, DefDBName, DefSchemaName, UserID, SID) EXEC sp_helpuserUPDATE #g SET DBName=''?'' WHERE DBName IS NULL'SELECT * FROM #G[/code]It's straightforward to select distinct from those.HTH,VedranSat, 13 Oct 2012 18:16:18 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1372392-392-1.aspxI need to determine the unique combinations of group permissions assigned to users. Currently, permissions are assigned to a user. I'm working on changing our model to assign permissions to a role, and assigning a role to a user.I believe I need to generate a query to PIVOT/Select DISTINCT with Row_Number/UNPIVOT the data, but I haven't been able to structure the query.We have nearly 15000 users and over 1000 groups, so I can't manually type the PIVOT/UNPIVOT column names. On the plus side, I don't care what the intermediate columns names will be as they'll only be used in this statement.I'm starting with:[u]Name GroupName[/u][i]Tom AdminTom AccountingTom SupportTom LegalDick AccountingDick LegalHarry SupportMary SupportNULL Restricted[/i]and need to finish with this[u]RoleNum GroupName[/u][i]1 Admin1 Accounting1 Support1 Legal2 Accounting2 Legal3 Support[/i]Notice the Support group only shows up once even though two people belong to that group, and the Restricted group does not appear because no one is currently assigned.I've created some sample tables to play with if anyone is interested in helping.:w00t:[code="sql"]CREATE TABLE [dbo].[groups]( [GroupID] [int] IDENTITY(1,1) NOT NULL, [GroupName] [varchar](50) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [NAME] [varchar](10) NULL) ON [PRIMARY]GOCREATE TABLE [dbo].[PersonGroups]( [PersonID] [int] NULL, [GroupID] [int] NULL) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[groups] ON;BEGIN TRANSACTION;INSERT INTO [dbo].[groups]([GroupID], [GroupName])SELECT 1, N'Admin' UNION ALLSELECT 2, N'Accounting' UNION ALLSELECT 3, N'Support' UNION ALLSELECT 4, N'Legal' UNION ALLSELECT 5, N'Restricted'COMMIT;RAISERROR (N'[dbo].[groups]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;GOSET IDENTITY_INSERT [dbo].[groups] OFF;SET IDENTITY_INSERT [dbo].[person] ON;BEGIN TRANSACTION;INSERT INTO [dbo].[person]([PersonID], [NAME])SELECT 1, N'Tom' UNION ALLSELECT 2, N'Dick' UNION ALLSELECT 3, N'Harry' UNION ALLSELECT 4, N'Mary'COMMIT;RAISERROR (N'[dbo].[person]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;GOSET IDENTITY_INSERT [dbo].[person] OFF;BEGIN TRANSACTION;INSERT INTO [dbo].[PersonGroups]([PersonID], [GroupID])SELECT 1, 1 UNION ALLSELECT 1, 2 UNION ALLSELECT 1, 3 UNION ALLSELECT 1, 4 UNION ALLSELECT 2, 2 UNION ALLSELECT 2, 4 UNION ALLSELECT 3, 3 UNION ALLSELECT 4, 3COMMIT;RAISERROR (N'[dbo].[PersonGroups]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;GO[/code]Fri, 12 Oct 2012 20:27:41 GMTwhenriksen