Wednesday, September 2, 2015

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.


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.



No comments: