0

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 )

3

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;
  • Hello Aaron. Thanks again for the fast answer. But why I can only see SysAdmin Logins? I comented the "where" but its showing me only SysAdmin. Is there a way to show every Login with your Role? I'm saying this because i already did this some time before to list sysadmin logins ( but they should not be ). Again, Thanks. – Racer SQL Mar 3 '15 at 13:43
  • Thanks. I was writing the comment when you updated. When it's NULL, it's because he's Public, right? – Racer SQL Mar 3 '15 at 13:44
  • @Rafael Try an outer join or the second query. It should list other role members too but with an inner join it would exclude anyone not in a role. (Sounds like you don't have any members of any other roles - this isn't specific to sysadmin.) – Aaron Bertrand Mar 3 '15 at 13:44
  • Thanks Aron. It's what i Need. Think i need to study a little more about SQL sys tables. Again, thank you. I'm really learning a lot with your answers. – Racer SQL Mar 3 '15 at 13:49
0

You can get the server principles through the following query:

SELECT name, principal_id, sid
FROM sys.server_principals
WHERE type_desc = 'SERVER_ROLE'

This works in every database, so you do not need to target a specific database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.