Pivot Database Names

  • 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.

    (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''
    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



    You must be logged in to view attached files.
  • 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.

  • 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;
  • 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