find permissions assosiated with LOGINS

find permissions assosiated with LOGINS

Below Query will give privileges related to LOGINS only. (Not User related)

select A.name 'LOGIN NAME',Create_date as [Create Date] ,A.type_desc [Authentication Type],
 A.default_database_name 'Default DB',Case B.role_principal_id
 When 2 Then 'Public'
 When 3 Then 'SysAdmin'
 When 4 Then 'SecurityAdmin'
 When 5 Then 'ServerAdmin'
 When 6 Then 'SetupAdmin'
 When 7 Then 'ProcessAdmin'
 When 8 Then 'DiskAdmin'
 When 9 Then 'DBcreator'
 When 10 Then 'BulkAdmin'
 End as 'Server Privilege'
 from sys.server_principals A left outer join sys.server_role_members B
 on A.principal_id=B.member_principal_id
 where
 Sid in (Select Sid from sys.syslogins) and 
 type in ('U','G','S')

Hope above query helps

Thanks