SQL Server Login Management using LOGINPROPERTY function

By:   |   Comments (9)   |   Related: > Auditing and Compliance


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.


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
 l.[type] IN ('U', 'G', 'S', 'C', 'K') 
 AND l.principal_id NOT BETWEEN 101 AND 255 -- ##MS% certificates
 ( 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":

Conditional formatting
  • for the fields - "PasswordPolicyEnforced", "PasswordExpirationEnabled":
  • for the field - "DaysUntilExpiration":

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

Run the report and review the results.


  • 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', 
  @email_address=N'[email protected]'

Create alert using the error message we have created above:

EXEC msdb.dbo.sp_add_alert @name=N'SQL Login Change Password - action required', 

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')
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  ); 

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.
  • UPDATE - Click here for the RDL file for this tip.

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Svetlana Golovko Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, February 27, 2016 - 12:57:13 PM - Svetlana Back To Top (40811)

Hi Pallavi


The easiest and quickest way is to check default SQL Server trace files. They will have infomation about login, host and objects created/altered. Unfortunately if your server is very busy the fiels will be overwritten. But if you didn't setup any monitoring/audit in advance this is your only option.




Friday, February 26, 2016 - 5:21:37 AM - Pallavi Back To Top (40796)



I am trying to find out a way to see if somebody is upto mischief in my database. a single user login is used by 3-4 developers and now i want to find out who has done what in last 24hrs. which SP they have worked on or what they did after loginin. A report maybe to see what has been changed from which IP the command hass been triggered. 


i dont know if I am being able to put across my thoughts

Monday, September 29, 2014 - 9:42:43 PM - Svetlana Golovko Back To Top (34763)

The tip has been updated. You can download the RDL file for the report now.




Thursday, September 25, 2014 - 7:28:53 AM - Chirag Khatsuriya Back To Top (34710)

Great Tip Svetlana!!!

Wednesday, April 30, 2014 - 11:30:50 AM - Ranga Back To Top (30569)

Great tip. Would love to have the ssrs .rdl file , can you please share ?

Tuesday, April 23, 2013 - 8:22:22 AM - Junior Galv„o - MVP Back To Top (23508)

Congratulations for this post.


Very nice.

Monday, April 22, 2013 - 10:35:20 AM - Ed - sqlscripter Back To Top (23480)

Nice article, here is a very simple query to view sql login properties.


Set nocount ON
Select @@servername as 'Server_Name'
select Convert(varchar(25),[name]), is_disabled, is_policy_checked, is_expiration_checked,
 create_date, modify_date from sys.sql_logins
where is_disabled = 1 or is_policy_checked = 1 or is_expiration_checked = 1

Wednesday, March 27, 2013 - 6:13:24 PM - Svetlana Golovko Back To Top (23039)

Thank you, Juno

Friday, March 22, 2013 - 12:30:17 AM - juno tiwari Back To Top (22949)

Nice Post 

get free sql tips
agree to terms