Scripting out server roles of login(s) in SQL Server

To view permissions of a login, we normally query sp_helplogins. This gives details of permission granted to login in each database plus brief information of the login (like sid, default database etc.), but this system stored proc is limited to database roles, it does not give us details of server roles assigned to that login.

We can see server roles of a login using GUI tool or by using syslogins table -

select name , sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin
from master..syslogins
where (sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin) <> 0

(In above query where clause is used to select only logins that have atleast one server role)

If you want a report for such logins, you can use below query

select name, 'Roles : '
+ case when sysadmin=1 then 'sysadmin' + ' ' else '' end
+ case when securityadmin=1 then 'securityadmin' + ' ' else '' end
+ case when serveradmin=1 then 'serveradmin' + ' ' else '' end
+ case when setupadmin=1 then 'setupadmin' + ' ' else '' end
+ case when processadmin=1 then 'processadmin' + ' ' else '' end
+ case when diskadmin=1 then 'diskadmin' + ' ' else '' end
+ case when dbcreator=1 then 'dbcreator' + ' ' else '' end
+ case when bulkadmin=1 then 'bulkadmin' + ' ' else '' end as Roles
from master..syslogins
where (sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin) <> 0

Similarily, while copying logins from one server to other, we sometime miss the server roles of logins, we can use following to generate script for server roles

select
case when sysadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''sysadmin''' end, case when securityadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''securityadmin''' end,
case when serveradmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''serveradmin''' end,
case when setupadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''setupadmin''' end,
case when processadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''processadmin''' end,
case when diskadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''diskadmin''' end,
case when dbcreator=1 then 'exec sp_addsrvrolemember '''+name+''' , ''dbcreator''' end,
case when bulkadmin=1 then 'exec sp_addsrvrolemember '''+name+''' , ''bulkadmin''' end
from syslogins where (sysadmin securityadmin serveradmin setupadmin processadmin diskadmin dbcreator bulkadmin) <> 0