DBA- Server Login name , DB login name along with their roles/memebers in sql server

 

Below Query  help  bring the  server  login name , data base level login name  along with their roles/members. 

This query will help us to validate the roles/ members are mapped correctly or not. 

USE master 

GO

CREATE TABLE #temp

(

id INTEGER  IDENTITY(1,1),

DatabasseName VARCHAR(100),

login_name VARCHAR(500),

database_user VARCHAR(500),

role_name VARCHAR(500),

user_type VARCHAR(500),

authentication_type_desc VARCHAR(500)

)

EXEC sp_foreachdb 

'

USE ?

INSERT INTO #temp (DatabasseName,login_name,database_user,role_name,user_type,authentication_type_desc)

SELECT

''?'' AS DatabasseName,

    sp.name AS login_name,

    dp.name AS database_user,

    dr.name AS role_name,

    dp.type_desc AS user_type,

    dp.authentication_type_desc

FROM 

    sys.server_principals sp

 JOIN  

    sys.database_principals dp ON sp.sid = dp.sid

LEFT JOIN 

    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

LEFT JOIN 

    sys.database_principals dr ON drm.role_principal_id = dr.principal_id

WHERE 

    sp.type IN (''S'',''U'', ''G'') AND dp.type IN (''S'', ''U'', ''G'')

ORDER BY 

    sp.name, dr.name 

'

SELECT  *  FROM #temp

WHERE login_name ='svcTMWorks-trng' 

--- DB Login names with their roles

SELECT

    sp.name AS login_name,

    dp.name AS database_user,

    dr.name AS role_name,

    dp.type_desc AS user_type,

    dp.authentication_type_desc

FROM 

    sys.server_principals sp

LEFT JOIN  

    sys.database_principals dp ON sp.sid = dp.sid

LEFT JOIN 

    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

LEFT JOIN 

    sys.database_principals dr ON drm.role_principal_id = dr.principal_id

WHERE 

    sp.type IN ('S', 'U', 'G') AND dp.type IN ('S', 'U', 'G')

AND sp.name  ='svcTMWorks-trng'

ORDER BY 

    sp.name, dr.name;