SELECT a.[name]
,CASE WHEN (IS_SRVROLEMEMBER('sysadmin', a.[name])) = 1 THEN 'YES'
ELSE 'NO' END AS [is_sysadmin]
,CASE WHEN (IS_SRVROLEMEMBER('securityadmin', a.[name])) = 1 THEN 'YES'
ELSE 'NO' END AS [is_securityadmin]
,a.[type_desc]
,a.[is_disabled]
,a.[default_database_name]
,b.[createdate]
FROM [master].[sys].[server_principals]
a
INNER JOIN [master].[sys].[syslogins] b
ON a.[name] = b.[name]
WHERE a.[type] NOT IN ('R','C')
AND a.[is_disabled] = 0
ORDER BY [is_sysadmin] DESC, [type_desc], [name]
;
