Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

SQL Server Login Management using LOGINPROPERTY function

MSSQLTips author Svetlana Golovko By:   |   Read Comments (7)   |   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:

Layout

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")  

Conditional formatting
  • 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.
Actions

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


Last Update: 3/21/2013


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.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Monday, September 29, 2014 - 9:42:43 PM - Svetlana Golovko Read The Tip

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

 

Thanks,

Svetlana


Thursday, September 25, 2014 - 7:28:53 AM - Chirag Khatsuriya Read The Tip

Great Tip Svetlana!!!


Wednesday, April 30, 2014 - 11:30:50 AM - Ranga Read The Tip

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 Read The Tip

Congratulations for this post.

 

Very nice.


Monday, April 22, 2013 - 10:35:20 AM - Ed - sqlscripter Read The Tip

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 Read The Tip

Thank you, Juno


Friday, March 22, 2013 - 12:30:17 AM - juno tiwari Read The Tip

Nice Post 




 
Sponsor Information