SQL Server Login Management using LOGINPROPERTY function

By: Svetlana Golovko   |   Read Comments (4)   |   Related Tips: More > Auditing and Compliance

Problem

One of the Support Analysts asked me if it is possible to send him notifications 5 days before a user's password expires for a SQL Server Standard login. He wants to be proactive and help users reset their passwords before they expired. The application uses SQL Server authentication and does not have password management functionality.  Check out this tip to learn more.

Solution

In one of our last tips we have provided several scripts for the SQL Server audit, including logins audit. But there is more to it.  DBAs can dig deeper and see how many bad login attempts a user had, when was the last time the password was set and get other useful audit related information using LOGINPROPERTY function. One of the past tips covers this function and it's usage in details.

In this tip we will provide a SQL Server Reporting Service (SSRS) report for DBAs that could be emailed and reviewed daily. Also we will provide scripts to setup an alert for the Support Analyst, to notify them about upcoming passwords expirations.

SQL Server Audit Logins Report for DBAs

Report Requirements:

The report will display a list of logins that meet the following criteria:

• Created or modified during the last 24 hours
• Expired logins
• Logins that will expire in 5 days or sooner
• Locked out logins
• Disabled logins
• Logins that have access denied
• Logins with Password Policy not checked
• Logins with Password Expiration not checked
• Logins with more than 2 bad password entries
• Logins that must change the password when user logs in next time to SQL Server

Create the Report:

If you are not familiar with SSRS a good place to start is this tutorial.

Create the new report using the query below as a dataset:

SELECT  l.name, 
 CAST(CASE sp.[state] WHEN N'D' THEN 1 ELSE 0 END AS bit) AS DenyWindowsLogin,
 CASE WHEN N'U' = l.type THEN 0 
  WHEN N'G' = l.type THEN 1 
  WHEN N'S' = l.type THEN 2 
  WHEN N'C' = l.type THEN 3 
  WHEN N'K' = l.type THEN 4 END AS LoginType,
 CAST(CASE WHEN (sp.[state] IS NULL) THEN 0 ELSE 1 END AS bit) AS HasAccess,
 CAST(sl.is_policy_checked AS bit) AS PasswordPolicyEnforced,
 CAST(sl.is_expiration_checked AS bit) AS PasswordExpirationEnabled,
 l.create_date AS CreateDate,
 l.modify_date AS DateLastModified,
 LOGINPROPERTY(l.name, N'BadPasswordCount')  AS BadPasswordCount,
 LOGINPROPERTY(l.name, N'BadPasswordTime')  AS BadPasswordTime,
 LOGINPROPERTY(l.name, N'DaysUntilExpiration') AS DaysUntilExpiration,
 LOGINPROPERTY(l.name, N'IsExpired')  AS IsExpired,
 LOGINPROPERTY(l.name, N'IsLocked') AS IsLocked,
 LOGINPROPERTY(l.name, N'IsMustChange') AS IsMustChange,
 LOGINPROPERTY(l.name, N'LockoutTime') AS LockoutTime,
 LOGINPROPERTY(l.name, N'PasswordLastSetTime') AS PasswordLastSetTime,
 l.is_disabled AS IsDisabled
FROM sys.server_principals AS l
  LEFT OUTER JOIN sys.server_permissions AS sp 
  ON sp.grantee_principal_id = l.principal_id 
   AND sp.[type] = N'COSQ' -- Connect permissions
  LEFT OUTER JOIN sys.sql_logins AS sl 
  ON sl.principal_id = l.principal_id
  LEFT OUTER JOIN sys.credentials AS c 
  ON c.credential_id = l.credential_id
WHERE
 l.[type] IN ('U', 'G', 'S', 'C', 'K') 
 AND l.principal_id NOT BETWEEN 101 AND 255 -- ##MS% certificates
 AND 
 ( sp.[state] = N'D'  --  DenyWindowsLogin
  OR sp.[state] IS NULL -- HasAccess
  OR CAST(sl.is_policy_checked AS bit) = 0
  OR CAST(sl.is_expiration_checked AS bit) = 0
  OR l.create_date > GETDATE()-1
  OR l.modify_date  > GETDATE()-1
  OR l.is_disabled > 0
  OR LOGINPROPERTY(l.name, N'DaysUntilExpiration')<= 5
  OR LOGINPROPERTY(l.name, N'IsExpired') > 0 
  OR LOGINPROPERTY(l.name, N'IsLocked') > 0 
  OR LOGINPROPERTY(l.name, N'IsMustChange') > 0 
  OR LOGINPROPERTY(l.name, N'BadPasswordCount')  > 2
 )

Create a simple table layout and use all of the fields from the query above:

Set conditional formatting to display text in red (using "Color" property) as following:

• for the fields - "DenyWindowsLogin", "IsDisabled", "BadPasswordCount", "IsExpired", "IsMustChange", "IsLocked":

 =iif(Value=0,"Black","Red")  

• for the fields - "PasswordPolicyEnforced", "PasswordExpirationEnabled":

 =iif(Value=True,"Black","Red")  

• for the field - "DaysUntilExpiration":

 =iif(Value>5,"Black","Red")   

Read this tip to find out more about SSRS conditional formatting.

Run the report and review the results.

Actions:

• Review and delete (if required) disabled logins and/or logins that have access denied issues.
• Review new or modified logins (created or modified during the last 24 hours).
• Review and update logins with password policy and expiration issues.
• Contact users that had many bad passwords attempts or that are locked out. Unlock login(s) if required.
• Contact users that have expired login or users whose login will expire soon (to reset the password).
• Contact users that “must change” their password and the "password last set" time exceeds you password policy.

Configure SQL Server Agent Operator, Alert and Job for the Support Analyst

We will use a query that utilizes the same (LOGINPROPERTY) function. Also, we will create custom error message, alert, operator and job that will run daily to monitor the logins. Read more about SQL Server Agent (including Notifications and Operators) here.

First, create a custom error message (you can use any number above 50000 that is not in use in your environment):

EXEC msdb.dbo.sp_addmessage  
 @msgnum = 60001,  
 @severity = 10, 
 @msgtext = 'Password is about to expire (or expired) for the user(s): %s. 
      Please, contact DBA and/or user(s) to reset the password(s).', 
 @with_log ='TRUE' 

Create operator:

EXEC msdb.dbo.sp_add_operator @name=N'Support_App1', 
  @enabled=1, 
  @weekday_pager_start_time=90000, 
  @weekday_pager_end_time=163000, 
  @pager_days=62, 
  @email_address=N'Support_App1@domain.com'

Create alert using the error message we have created above:

EXEC msdb.dbo.sp_add_alert @name=N'SQL Login Change Password - action required', 
  @message_id=60001, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=0, 
  @include_event_description_in=1

Create notification for the Support Analyst:

EXEC msdb.dbo.sp_add_notification @alert_name=N'SQL Login Change Password - action required',
   @operator_name=N'Support_App1', @notification_method = 1

Now create the SQL Server Agent Job that will run daily.  The SQL Server Agent Job will have one Transact-SQL step. The command for the step is:

IF EXISTS (SELECT name, LOGINPROPERTY(name, N'DaysUntilExpiration')  
  FROM sys.server_principals 
  WHERE type = 'S'
                    AND LOGINPROPERTY(name, N'DaysUntilExpiration') < 6
                    AND default_database_name = 'APP1_Prod')
BEGIN
DECLARE @str VARCHAR(200)
SELECT @str = COALESCE(@str + ',', '') + name  FROM sys.server_principals 
 WHERE type = 'S' AND LOGINPROPERTY(name, N'DaysUntilExpiration') < 6
  AND default_database_name = 'APP1_Prod'
RAISERROR (60001, 10, 1, @str  ); 
END

You can add exceptions to the notifications by filtering this statement, for example:

• exclude "sa" and application's service account:

....
 AND default_database_name = 'APP1_Prod'
 AND name NOT IN ('sa', 'app_service_login')
.... 

• exclude disabled or locked out logins:

....
 AND default_database_name = 'APP1_Prod'
 AND is_disabled = 0
 AND LOGINPROPERTY(name, N'IsLocked') = 0
.... 

Next Steps

• Run the script we used in SSRS report on multiple servers using Central Management Server as in this tip.
• Review and fix issues.
• Schedule the report to run daily and review it on a daily basis.
• Retain the latest reports in case you need to provide evidence of the login review to auditors.
• Find out if your Support Analyst wants to be notified about expiring passwords.

About the author

DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code