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;
-- 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.