0
(0)

Below query can be used to get SQL Server logins with server role in SQL Server.

SELECT @@servername as Server, sp.name Login_Name
,Account_Type = CASE sp.type_desc
WHEN 'Windows_Group'
THEN 'Windows Group'
WHEN 'WINDOWS_LOGIN'
THEN 'Windows Login'
ELSE 'SQL Login'
END
,CASE
WHEN sysadmin = 1
THEN 'Yes'
ELSE 'No'
END AS Sysadmin
,CASE
WHEN securityadmin = 1
THEN 'Yes'
ELSE 'No'
END AS securityadmin
,CASE
WHEN serveradmin = 1
THEN 'Yes'
ELSE 'No'
END AS serveradmin
,CASE
WHEN setupadmin = 1
THEN 'Yes'
ELSE 'No'
END AS setupadmin
,CASE
WHEN processadmin = 1
THEN 'Yes'
ELSE 'No'
END AS processadmin
,CASE
WHEN diskadmin = 1
THEN 'Yes'
ELSE 'No'
END AS diskadmin
,CASE
WHEN dbcreator = 1
THEN 'Yes'
ELSE 'No'
END AS dbcreator
,CASE
WHEN bulkadmin = 1
THEN 'Yes'
ELSE 'No'
END AS bulkadmin
FROM sys.syslogins sl
JOIN sys.server_principals sp ON sl.sid = sp.sid
WHERE sp.name NOT LIKE '%##%'
AND sp.name NOT LIKE '%NT%'

Sample Output:

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.