SQL Server Accounts Locked Out

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.

Problem

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.

The Code

1

You can see we need a few variables defined.

2

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.

3

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.

4

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.

5

This is an example what the email looks like when received.

Conclusion

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.

Cheers!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s