Hello,
I have been trying and failing for days to create a query, or SSRS report, that makes a column for each database on the server, with each row representing a logon. The values of the database named columns would be the database role that logon has in the database. I have been trying with pivot but I can't find examples that work with these data types. The closest I have been able to find is this query, which STUFFS the database roles for users that have multiple roles in the same database into one row. I have been trying to combine something like this with a pivot, but not getting it close enough to even run.
I attached a mock up picture of what I am trying to produce. But here is the query I describe above. Thanks for any help or tips.
DECLARE @DB_Users TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(200),Create_date datetime,Modify_date datetime, Orphaned varchar(5))
INSERT @DB_Users
EXEC sp_MSforeachdb
'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,prin.create_date,prin.modify_date,
Orphaned = CASE
WHEN sprin.name IS NULL THEN ''Yes''
ELSE ''No''
END
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
LEFT OUTER JOIN sys.server_principals sprin ON prin.sid = sprin.sid
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'' and prin.type in (''S'',''G'',''U'')'
--SELECT UserName,DBName,AssociatedRole,Create_date,Modify_date,Orphaned FROM @DB_Users
--ORDER BY username, DBName
SELECT username,dbname,
STUFF((SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
FROM @DB_USers user2
WHERE user1.DBName=user2.DBName AND user1.UserName=user2.UserName
FOR XML PATH('')),1,1,'') AS role_member
FROM @DB_USers user1
where logintype <> 'DATABASE_ROLE' AND user1.UserName <> 'dbo (sa)'
GROUP BY dbname,username ,logintype
ORDER BY DBName,role_member
May 11, 2022 at 8:03 am
I have not taken the time to analyse your code, but I would suggest leaving the pivoting to SSRS. If you can get results in the form
Username, DBName, Roles
(where Roles is a comma-delimited list)
the rest is trivial in a tablix.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
May 11, 2022 at 8:38 am
You can take a look at the following articles by Jeff Moden on dynamically creating PIVOTs and CROSS_TABs
https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs
If you convert your @DB_Users table variable to a temp table, the following code will dynamically create the cross-tab in SQL
DECLARE @Query nvarchar(MAX);
SET @Query = 'WITH cteData AS (
SELECT u.UserName
, u.LoginType
, u.DBName
, role_member = STUFF(( SELECT '','' + CONVERT( varchar(500), r.AssociatedRole )
FROM #DB_Users AS r
WHERE u.DBName = r.DBName
AND u.UserName = r.UserName
FOR XML PATH( '''' )), 1, 1, '''' )
FROM #DB_Users AS u
WHERE u.LoginType <> ''DATABASE_ROLE''
AND u.UserName <> ''dbo (sa)''
GROUP BY u.UserName, u.DBName, u.LoginType
)
SELECT d.UserName, d.LoginType ';
SELECT @Query += '
, ' + QUOTENAME(u.DBName) + ' = MAX(CASE WHEN d.DBName = ''' + u.DBName + ''' THEN d.role_member END)'
FROM #DB_Users AS u
GROUP BY u.DBName
ORDER BY u.DBName;
SET @Query += '
FROM cteData AS d
GROUP BY d.UserName, d.LoginType
ORDER BY d.UserName, d.LoginType;';
--PRINT (@Query);
EXEC sys.sp_executesql @stmt = @Query;
May 11, 2022 at 2:16 pm
You are a god!
Thank you so much this has been tremendously helpful.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply