Working with SQL Server logins

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