I recently had a requirement to document the assignment of navigator bars as part of a migration, showing which navigator bars were assigned to which groups/users, before and after the migration. As we had over 100 groups, 450 support staff and some 20 or navigator bars, the idea of doing this manually did not appeal. Consequently, I developed a query to return the information I needed and thought it might be of use to others:
SELECT IsNull(G.[CODE], '--Default--') As 'Group', IsNull(P.[FNAME] + ' ' + P.[NAME] + ' (' + P.[CODE] + ')', 'Default') As 'Support Staff', N.[NAVNAME] As 'Navigator Bar' FROM dbo.NAMSYSHTMLASSIGNS A JOIN dbo.NAMSYSHTMLNAVIGATORS N ON N.[SEQUENCE] = A.[HTMLSEQ] LEFT JOIN _SMDBA_._GROUPS_ G ON G.[SEQUENCE] = A.[_GROUP_] LEFT JOIN _SMDBA_._PERSONNEL_ P ON P.[SEQUENCE] = A.[_MEMBER_] WHERE (G.[_INACTIVE_:] = 0 OR G.[_INACTIVE_:] IS NULL) AND (P.[_INACTIVE_:] = 0 OR P.[_INACTIVE_:] IS NULL) AND A.[TYPE] = 0 ORDER BY 1,2
It made the writting of my document somewhat simpler.