Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

When was the last time the SQL Server sa password changed?

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (3)   |   Related Tips: More > Security

Problem
With the rights that the sa login has by default in SQL Server, it is imperative to change this password on a regular basis whether it is monthly, quarterly or semi-annually.  In addition, as DBAs move on to other opportunities, it is wise to change the sa password as well.  Changing the sa password should be a relatively easy process requiring little to no impact on the organization.  Unfortunately, changing the sa password on a regular basis is not a common practice at most organizations, because the impacts of changing the password are unknown.  

Solution
The first step in the process is to find out when the sa password was last changed.  If this timeframe is unacceptable to your organization, then steps need to be taken to understand where the sa login is used and how the application can be modified to use another login. 

SQL Server 2000 - sa password changes

In SQL Server 2000 a documented process does not exist to determine when the sa password was changed.  The best means to determine if the sa password has changed is based on the value from the updatedate column in the master.dbo.syslogins table.  This value seems to be the only possible column to determine if any property (default database, default language, etc.) for the sa login has changed.  Since the sa properties do not change frequently, the value for this column should be a reasonable, but not an absolute indicator of when the sa password was last changed.  Reference the code below to determine the value for the sa login's updatedate column.

USE Master
GO
SELECT sid, [name], createdate, updatedate
FROM master.dbo.syslogins
WHERE [name] = 'sa'
GO

SQL Server 2005 - sa password changes

Unfortunately, SQL Server 2005 suffers from the same self documenting issue as SQL Server 2000 as it pertains to the last time the sa password has changed.  The modify_date of the sys.sql_logins catalog view can be used as an indicator of when the last property (default database, default language, etc.) for the login has changed.  Just as is the case with SQL Server 2000, this value does not guarantee the date\time stamp of the password change, but rather any property change.  Since the properties do not change frequently under normal circumstances this value can serve as a reasonable, but not an absolute indicator of when the sa password was last changed.  If other properties have changed, the modify_date is not a true indicator and other research/documentation is needed to determine the last sa password change date.

USE Master
GO
SELECT [name], sid, create_date, modify_date
FROM sys.sql_logins
WHERE [name] = 'sa'
GO

To address the risks of changing the sa password, stay tuned for the upcoming tips in the sa series from MSSQLTips.com

Next Steps

  • If you are scratching your head as to the last time the sa password was changed, then it is worth taking a look at your key SQL Servers
  • If the last time the sa password has changed is unacceptable to you, then begin to work with your team on taking the necessary steps to changing the sa password
    • In addition, stay tuned for the upcoming sa series tips on how to determine who and what applications are using the sa user name and password
  • In order to prevent the sa password from becoming stale in the future, be sure to put processes in place to change the sa password on a regular basis
  • For additional information from MSSQLTips.com check out the following:
    • The first tip in the sa series - When not to use the sa password
    • Stay tuned for the upcoming tips in the sa series:
      • Determine how to find out who and what applications are using the sa user name and password
      • Password management options for the sa login
    • Check out the MSSQLTips.com security related tips
  • Check out this tip on SQL Server 2005 DDL Auditing to track password changes for SQL Server 2005


Last Update: 12/27/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, April 04, 2008 - 3:31:38 AM - tosc Read The Tip

Hi,

in SQL Server 2005 you can use LOGINPROPERTY function to determine the last time the sa password has changed.

SELECT LOGINPROPERTY ('sa', 'PasswordLastSetTime') AS 'PasswordLastSetTime'

PasswordLastSetTime

2008-04-04 11:39:44.687

 


Friday, April 04, 2008 - 7:00:35 AM - admin Read The Tip

Tosc,

Thank you for the post.  That is a great tip and will include a new tip on the subject.

Thank you,
The MSSQLTips.com Team


Friday, May 24, 2013 - 5:16:57 AM - Shahid Read The Tip

 

Yes tosc you are right LOGINPORPERTY('Login_name', 'PasswordLastSetTime') is the right command to find out last password change date and time.

What Jeremy suggested will provide any modification happened last time e.g. login enable/disable date but not specifically just passwrod change date.

Thanks for your help.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.