fatih.bektas fatih.bektas - 6 months ago 28
SQL Question

How can i get the list of all database name with their username and roles in t-sql?

I'm trying to get the list of all database name with their username and roles. Like this:
image

Here's my code but i can get only dbname:

SELECT
LEFT(ltrim(rtrim(@@ServerName)), Charindex('\', ltrim(rtrim(@@ServerName))) -1) servername
,CONVERT(nvarchar,dec.local_net_address) AS IPAdress
,suser_sname(owner_sid) as 'LocalAdmin'
, db.name AS dbname
FROM sys.dm_exec_connections AS dec
CROSS JOIN sys.databases db
WHERE dec.session_id = @@SPID AND suser_sname(owner_sid) <> 'sa'

Answer

You can use like this:

DECLARE @DB_USers TABLE (
    DBName SYSNAME
    ,UserName SYSNAME
    ,LoginType SYSNAME
    ,AssociatedRole VARCHAR(max)
    ,create_date DATETIME
    ,modify_date DATETIME
    )

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 ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''

SELECT dbname
    ,username
    ,logintype
    ,create_date
    ,modify_date
    ,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 Permissions_user
FROM @DB_USers user1
GROUP BY dbname
    ,username
    ,logintype
    ,create_date
    ,modify_date
ORDER BY DBName
    ,username
Comments