Auditing Your SQL Server Environment Part I

By : Randy Dyess May 11, 2002

Printer friendly

Over the last few years, I have been a roving SQL Server DBA contractor, tending to work contacts in small and mid-size companies that involved organizing, documenting and tuning/optimizing existing SQL Servers in those companies. During this time, I have noticed one very disturbing truth over the years; no one seems to document anything. In fact, I was usually thankful if I could find something or someone who knew the sa passwords on each SQL Server installation, let alone knew anything else about their setups.
I have often been asked how I could go into a company that had over 50 SQL Server databases up and running on a dozen or more servers, that had no existing documentation, and that had no full-time DBA on staff, or had lost their DBA several months ago and a developer or manager was filling in, and ramp up to a level that I could function in a very short time frame. My answer was practice, and my file of stored procedures written over the years that I carried with me which allowed me to do quick audits of the SQL Server installations and databases so I could quickly produce documentation to work from.

This article is the first in a series that I intend to share these stored procedures and methods I have learned. You can use these to help you produce and learn a new environment if you move on or obtain another project at your existing company.

Determine If You Have a Problem

Many companies who use SQL Server with custom applications do so with the application connecting to SQL Server with SQL Server Authentication. I know that Microsoft would love for everyone to use Windows Authentication, or at least use an application role, but that just isn't the norm at this time.

There is a big problem with SQL Server Authentication, and that is with password handling. There is nothing that prevents a login from having a blank password, there is nothing that mandates a minimum length on passwords, nor is there a way to prevent users from using their login name as their password.

I was so thankful that with SQL Server 2000, you have to force the installation process to install SQL Server with a blank password for the sa login, instead of the default being a blank password.

It is with this in mind I would like to present a short stored procedure that will go into the system table sysxlogins and check user's passwords for those three shortcomings: no password present, the password being the same as the login name, and the password being only one character long.

Create this stored procedure in the master database on each of the servers you administer, and then execute it to perform a simple audit of the existing logins to determine if you have any logins with problem passwords.