Finding server level permission (principals) of a login

We create SQL Server login principal for allowing users to connect with the instance of SQL Server. A standard logins are the most common logins but some logins have special permission making them part of Server Roles. How can I quickly find assigned principals or server level permission, whether granted or denied for a login?

Simplest way is, using sys.login_token that shows one row for each server principal that is attached to the login.

Read more info on sys.login: https://msdn.microsoft.com/en-us/library/ms186740.aspx

Here is a sample code that shows the usage of it;

view plainprint?

1. <span style="text-align: left;">-- Create a SQL Server login  
2. CREATE LOGIN James WITH PASSWORD = 'Pa$$w0rd';  
3. GO  
4.   
5. -- Adding James to serveradmin fixed server role  
6. ALTER SERVER ROLE serveradmin ADD MEMBER James;  
7. GO  
8.   
9. -- Checking James assigned principal  
10. EXECUTE AS LOGIN = 'James';  
11. GO  
12.   
13. SELECT * FROM sys.login_token;  
14.   
15. REVERT;  
16. GO  
17. </span>  

-- Create a SQL Server login
CREATE LOGIN James WITH PASSWORD = 'Pa$$w0rd';
GO

-- Adding James to serveradmin fixed server role
ALTER SERVER ROLE serveradmin ADD MEMBER James;
GO

-- Checking James assigned principal
EXECUTE AS LOGIN = 'James';
GO

SELECT * FROM sys.login_token;

REVERT;
GO

As you see, Login Token of James is part of SQL Login, public Server Role, and serveradmin Server Role.