SQL Server ‘logins’ are what’s known as a server level ‘principal’. From BOL, “Principals are entities that can request SQL Server resources.” There are two types of logins, one where the credentials are stored in SQL Server and one where the credentials come from Windows, either locally or through Active Directory. So let’s begin with a list of our logins. Not surprising, the catalog view ‘sys.server_principals’ works nicely:
-- List logins on the instance SELECT name, sid, type_desc, create_date, default_database_name FROM sys.server_principals WHERE type = 'S' OR type = 'U' ORDER BY type_desc |
For the above example, the various ‘types’ are:
S = SQL Server login
U = Windows authenticated login
The catalog view ‘sys.sql_logins’ is also useful, but only shows information for logins that store credentials in SQL Server:
SELECT name, sid, create_date, default_database_name, is_policy_checked, is_expiration_checked FROM sys.sys_logins |
We can create a new login with the following code:
-- Create a SQL Server login called 'testuser', adhering -- to password expiration and policy standards CREATE LOGIN [testuser] WITH PASSWORD = 'StrongPasswordHere!', CHECK_EXPIRATION=ON, CHECK_POLICY=ON |
-- Create a Windows authenticated login -- called 'testuser' in the domain 'united' CREATE LOGIN [united\testuser] FROM WINDOWS, CHECK_EXPIRATION=ON, CHECK_POLICY=ON |
Conversely, we can delete a login:
-- Delete the 'testuser' SQL Server login DROP LOGIN [testuser] -- Delete the 'united\testuser' Windows authenticated login DROP LOGIN [united\testuser] |
Here’s more useful commands when working with logins:
-- Change 'testuser' password from 'StrongPasswordHere!' -- to 'AnotherStrongPassword!' ALTER LOGIN [testuser] WITH PASSWORD = 'AnotherStrongPassword!' -- Disable and enable the 'testuser' login ALTER LOGIN [testuser] DISABLE ALTER LOGIN [testuser] ENABLE |
