Those dreaded account lockouts. We all have to deal with them from time to time. I created a job to be notified when accounts become locked. That way I am aware of the issue and depending on the account, server and other factors I can deal with it with it in a timely manner.
When accounts become locked users can’t do their job or in worst case scenarios applications fail to work. I won’t go into details about application accounts locking other than saying it is an application design flaw.
You can see we need a few variables defined.
Then comes the formatting for the HTML output, this can be a little cumbersome if you don’t know HTML or want it to be really visually appealing. Personally, I just want the basics and that means looking better than plan old text display. It also provides a little more control over the output.
Then creating a temp table and insert the results. The heart of this code is query. Without the use of CAST and CONVERT or you will get an error when trying to insert into the temp table. I insert only for row counts, I then use @@ROWCOUNT to set a flag to send/not send the email notification. I did try and get the row counts from the query within the @tableHTML but always got one extra row even though no accounts were locked.
If there are accounts locked, the sp_send_dbmail code is used sending an email. If you look at @emailsubject the server name is inserted after some descriptive wording. This used for identification for the DBA group as well as the help desk when creating the tickets and the project it gets routed to. Makes it much easier to unlock accounts if you know what server they are on.
This is an example what the email looks like when received.
If you have unlock user accounts frequently or just have a need to be notified for awareness of accounts being locked this is one way you can do it. Are there others? Sure there are, but this is what I have come up with for now. The code is based off of this link from msdn on sp_send_dbmail http://msdn.microsoft.com/en-us/library/ms190307.aspx
I have added this code to my DBA Toolbox. To see some other snippets look under the DBA Toolbox category.