Did you change the “sa” password recently? As a DBA, you should be aware that there is a great security risk linked to the sa account. You should always use strongs password for this account and change the password frequently.
You can easily check when the “sa” password was last changed in SQL Server 2005 by executing the following T-SQL code:
SELECT LOGINPROPERTY ('sa', 'PasswordLastSetTime')
The LOGINPROPERTY function gives you lots of information on the logins properties and password policy information for these logins.
Another thing you can do with this function is to look for security attacks. For example if you want to look for brute-force or dictionnary attack on the “sa” account, you can use the following query:
SELECT LOGINPROPERTY ('sa', 'BadPasswordCount')
This will return the number of failed consecutive attempts to login since the last successful login. So if this value goes over a certain value, you can easily see that something might be wrong.
Here is the complete list of properties you can query for using the LoginProperty function:
Returns the number of consecutive attempts to log in with an incorrect password.
Returns the time of the last attempt to log in with an incorrect password.
Returns the number of days until the password expires.
Returns the SQL Server login default database as stored in metadata or master if no database is specified. Returns NULL for non-SQL Server provisioned users; for example, Windows authenticated users.
Returns the login default language as stored in metadata. Returns NULL for non-SQL Server provisioned users, for example, Windows authenticated users.
Returns the length of time the login has been tracked using the password-policy enforcement mechanism.
Returns information that will indicate whether the login has expired.
Returns information that will indicate whether the login is locked.
Returns information that will indicate whether the login must change its password the next time it connects.
Returns the date when the SQL Server login was locked out because it had exceeded the permitted number of failed login attempts.
Returns the hash of the password.
Returns the date when the current password was set.
Differences between temporary tables and tables variables
How to insert a file in an image column in SQL Server 2005
How to add a row number in an SQL Query