Tip & How-To about Computers & Internet
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 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.
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.
SELECT sid, [name], createdate, updatedate
WHERE [name] = 'sa'
GOSQL 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 ime 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.
SELECT [name], sid, create_date, modify_date
WHERE [name] = 'sa'
GOTo address the risks of changing the sa password, stay tuned for the upcoming tips in the sa series from MSSQLTips.com.
Posted by Tely... on
Jul 04, 2014 | Microsoft SQL Server 2005 Enterprise...
Jun 27, 2014 | Microsoft SQL Server 2005 Enterprise...
231 people viewed this tip
Usually answered in minutes!