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.
No comments:
Post a Comment