SQL Server – How to List LOGIN and ROLE in SQL Server 2008

loginsrolescriptingSecuritysql server

I'm looking for an answer but I can't find it. Just a lot of long scripts.

I need to list Login | Role (sysadmin, dbcreator, etc ) but I can't find a table to join.

I have this for now:

SELECT 
    name AS Login_Name, type_desc AS Account_Type
FROM 
    sys.server_principals 
WHERE 
    TYPE IN ('U', 'S', 'G')
    AND name NOT LIKE '%##%'
ORDER BY 
    name, type_desc

I need another column with the role.

With table should I search for it?

Thanks.

EDIT:

I see that sys.syslogins has some information with binary.

Is it possible to use a CASE here? ( Programming is not my best )

Best Answer

No, don't use sys.syslogins - this is a deprecated compatibility view that is only there so people have time to change their existing code based on SQL Server 2000. Also, I'm completely unclear how you think a CASE expression will help you. You need to join to sys.server_role_members to find out which logins belong to which roles, and then back to sys.server_principals again to get the name of each role.

SELECT p.name AS Login_Name, p.[type_desc] AS Account_Type,r.name AS [Role]
FROM sys.server_principals AS p
LEFT OUTER JOIN sys.server_role_members AS rm
ON p.principal_id = rm.member_principal_id
LEFT OUTER JOIN sys.server_principals AS r
ON rm.role_principal_id = r.principal_id
AND r.[type] = 'R'
WHERE p.[type] IN ('U', 'S', 'G')
and p.name not like '%##%'
ORDER BY p.name, p.[type_desc];

(I'm not sure what value you expect from the second ORDER BY column here.)

If you want only a single row per login:

SELECT p.name AS Login_Name, p.[type_desc] AS Account_Type,
  Roles = STUFF((SELECT N',' + r.name
    FROM sys.server_role_members AS rm
    INNER JOIN sys.server_principals AS r
    ON rm.role_principal_id = r.principal_id
    WHERE rm.member_principal_id = p.principal_id
    ORDER BY r.name 
    FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,N'')
FROM sys.server_principals AS p
WHERE p.[type] IN ('U', 'S', 'G')
and p.name not like '%##%'
ORDER BY p.name;
Related Question