Most of the Organisation enforces password expiration policies to manage the lifespan of a password. When SQL Server enforces password expiration policy, users must reset the password. Steps to set up an automated email notification 7 days prior to the actual date of expiry are:-
Step 1: Configure Database Mail
You can go here to setup DatabaseMail if you don't have one alreadyStep 2: Create Stored Procedure in the DBA utility database or any database using the below script. The script makes use of the SQL Server DatabaseMail & SQL Server Agent feature to send Password Expiration Notification either to the Database Team or Application Team 1 week before the expiry date.
USE DBA_DB --Change Database Name GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[PasswordExpiration_SP] AS IF object_id('tempdb..#Password') IS NOT NULL DROP TABLE #Password DECLARE @count INT SELECT @@Servername AS ServerName ,'ITG' AS Env ,SL.name AS LoginName ,LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS DaysUntilExpiration ,LOGINPROPERTY(SL.name, 'IsExpired') AS IsExpired INTO #Password FROM sys.sql_logins AS SL WHERE is_expiration_checked = 1 AND is_disabled = 0 AND sl.name NOT LIKE '##%' AND DATEDIFF(dd, getdate(), DATEADD(DAY, CAST(LOGINPROPERTY(SL.name, 'DaysUntilExpiration') AS INT), GETDATE())) < 7 --Change the number of days here SET @Count = @@ROWCOUNT --select * from #Password IF @Count > 0 BEGIN BEGIN -- Email Querry-- DECLARE @Body VARCHAR(max) DECLARE @TableHead VARCHAR(max) DECLARE @TableTail VARCHAR(max) DECLARE @mailitem_id AS INT DECLARE @statusMsg AS VARCHAR(max) DECLARE @Error AS VARCHAR(max) DECLARE @Note AS VARCHAR(max) SET NOCOUNT ON; SET @mailitem_id = NULL SET @statusMsg = NULL SET @Error = NULL SET @Note = NULL SET @TableTail = '</table> <br/>Request you to reset the password before it expires<br/> <br/>Thanks<br/>DB Team</body></html>'; --HTML layout-- SET @TableHead = '<html><head>' + 'Hello,<br/> <br/> Greetings from DBA Team. <br/> <br/> Please reset password of below account(s). <br/> <br/> ' + '<style>' + 'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' + '</style>' + '</head>' + '<body><table cellpadding=0 cellspacing=0 border=0>' + '<tr bgcolor=#F6AC5D>' + '<td align=center><b>ServerName</b></td>' + '<td align=center><b>Env</b></td>' + '<td align=center><b>LoginName</b></td>' + '<td align=center><b>DaysUntilExpiration</b></td>' + '<td align=center><b>IsExpired</b></td></tr>'; --Select information for the Report-- SELECT @Body = ( SELECT [ServerName] AS [TD] ,[Env] AS [TD] ,[LoginName] AS [TD] ,[DaysUntilExpiration] AS [TD] ,[IsExpired] AS [TD] FROM #Password FOR XML raw('tr') ,Elements ) --convert(varchar(19), [PasswordLastSetTime] + ' ' + @modtime, 20) -- Replace the entity codes and row numbers SET @Body = Replace(@Body, '_x0020_', space(1)) SET @Body = Replace(@Body, '_x003D_', '=') SET @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>') SET @Body = Replace(@Body, '<TRRow>0</TRRow>', '') SET @Body = @TableHead + @Body + @TableTail -- return output-- --Email EXEC msdb.dbo.sp_send_dbmail @recipients = 'mail_ID1;mail_ID2' ,--Specify the mail ID's for which notification is required @profile_name = '' ,-- Specify the Profile name @subject = 'Please Reset the password ' ,@body = @Body ,@body_format = 'HTML'; DROP TABLE #Password END END
Step 3: Schedule a SQL Server Agent job to execute the Stored Procedure created(in step 2) to run daily
Done!! I hope this helps 😊
No comments:
Post a Comment