Send login expiration notification alerts in sql server

On By ADsqlIn Uncategorized

Note:  Best viewed in Internet Explorer 6 and above

Implementation: How to send login expiration notification alerts in SQL Server.

Scenario: SQL Server login policies are same as the windows login policies.Its inherit the property from windows login/password policies.

Now our target is to send mail notification before SQL Server login password get expired on production environment to avoid down time.

Infrastructure: Below queries and processes are tested with SQL server2005,2008,2008 R2,2012.

Steps:

  • I am going to put some value in newly and one time created table from below [sql_logins] table.You can check the column and needed value by firing below query.
    select * from sys.sql_logins
  • /*Create the table in any user/master database*/create table ExpiryNote
    (
    Sr_No int identity(1,1) not null,
    ServerName varchar(40) default @@servername,
    ServerType varchar(20) default ‘TestServer’, –server type whether Test Or Live Server
    [LoginName] sysname,
    LastModifiedDate datetime,
    DaysFromLastModified int –this will be our days of password expiration)
  • Kindly download above table script from below link.

https://app.box.com/s/njtims8qajtsxjrwu3efxlmo6fwbhhj0

  • /* create this procedure on respective DB and run it on daily basis before 2nd stored procedure.
    Simply create a job having two steps in it.1st step will run below 1st stored procedure and on successfully step,2nd stored procedure will be getting run in 2nd step.

Created by: Dharmesh Mishra
Date/Time:2nd July 2015,09:00 PM
Like my fb page:www.facebook.com/sqlserverkillers
Visit my blogs:lapsql.wordpress.com

  • */
    create procedure [sp_before_sp_PasswordExpiryMadeByDharmesh]
    as
    begin
    truncate table expirynote ——–will truncate the table firstINSERT INTO ExpiryNote ———————then will start puting new data
    (LoginName,LastModifiedDate,DaysFromLastModified)
    SELECT name,modify_date,DATEDIFF(DAY,modify_date,GETDATE())
    FROM sys.sql_logins
    where name in (‘test’)—Enter the login name for which you want to send mail notification before expiration
    end
  • Kindly download above 1st stored procedure from below location.

https://app.box.com/s/074916ij2jc8v00sm5k7clwvrarois04

  • /*Run this procedure in 2nd step in sql job*/create Procedure [sp_PasswordExpiryMadeByDharmesh]
    as
    begin
    declare @recipients varchar(max)
    declare @message varchar(5000)
    declare @query varchar(max)
    declare @subject varchar(112)
    declare @attach_results varchar(50)set @recipients = ‘dharmesh@xyz.com’
    set @message = ”
    set @query = ”
    set @subject = ‘Home_Pass_Expiry_Auto_Alert’
    set @attach_results = ‘True’IF Exists(select 1 from ExpiryNote)
    begin
    ———- case 2 starts
    beginDECLARE @xml NVARCHAR(MAX)
    DECLARE @body NVARCHAR(MAX)

    begin

    SET @xml = CAST((SELECT [Sr_No] AS ‘td’,”,[ServerName] AS ‘td’,”,[ServerType] AS ‘td’ ,”,[LoginName] AS ‘td’ ,”,[LastModifiedDate] AS ‘td’,”,[DaysFromLastModified] as ‘td’ from
    (select * from ExpiryNote) tab
    FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))
    SET @body ='<html><body><H3>
    Home_Pass_Expiry_Auto_Alert</H3>
    <tr>This is system generated Report, Please do not reply.</tr>
    <table border = 1>
    <tr bgcolor=”#C0C0C0″>
    <th> Sr_No </th> <th> ServerName </th> <th>ServerType </th> <th> LoginName</th> <th> LastModifiedDate </th> <th> DaysFromLastModified </th> </tr>’

    SET @body = @body + @xml +'</table></body></html>’

    ———- case 2 starts
    begin

    set @message = ‘Please Do not reply on this mail’

    exec msdb..sp_send_dbmail @profile_name = ‘dba’
    , @recipients=@recipients
    , @body = @body
    , @subject= @subject
    , @body_format =’HTML’

    end

    end
    end
    end

    else print(‘bye’)

    end

  • Kindly download above 1st stored procedure from below location.

https://app.box.com/s/rntd3va60tcfb8to72oc8azd1fqy9ogj

  • Once it is done,Schedule the job on daily basis which will send the status of SQL logins.Below is the screen shot for the same.

login expiration

  • This is how you can send login expiration notification alerts in sql server.
  • Please do comments when you find this useful or any suggestion on the same will be highly accepted.

One thought on “Send login expiration notification alerts in sql server”

Comments are closed.