How to Find Last Login on Sql Server
When was the concluding fourth dimension the SQL Server sa password changed?
By: | Updated: 2006-12-27 | Comments (iii) | Related: > Security
Problem
With the rights that the sa login has by default in SQL Server, it is imperative to alter this password on a regular ground whether it is monthly, quarterly or semi-annually. In add-on, as DBAs movement on to other opportunities, information technology is wise to alter the sa password also. Changing the sa password should be a relatively easy process requiring niggling to no touch on on the system. 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 stride in the process is to detect out when the sa password was last inverse. If this timeframe is unacceptable to your organization, and so steps demand to be taken to empathise where the sa login is used and how the awarding 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 all-time means to make up one's mind 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 make up one's mind if any property (default database, default language, etc.) for the sa login has changed. Since the sa backdrop practise not change often, the value for this column should be a reasonable, but not an absolute indicator of when the sa password was last inverse. Reference the code below to decide the value for the sa login's updatedate column.
Apply Chief
Become
SELECT sid, [name], createdate, updatedate
FROM chief.dbo.syslogins
WHERE [name] = 'sa'
GO
SQL Server 2005 - sa countersign changes
Unfortunately, SQL Server 2005 suffers from the same self documenting upshot as SQL Server 2000 as it pertains to the last fourth dimension the sa countersign has changed. The modify_date of the sys.sql_logins catalog view tin be used every bit an indicator of when the last property (default database, default linguistic communication, etc.) for the login has changed. Just as is the instance with SQL Server 2000, this value does not guarantee the date\fourth dimension postage of the password change, but rather whatever holding change. Since the properties do non alter frequently under normal circumstances this value can serve as a reasonable, merely not an absolute indicator of when the sa countersign 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 [proper name], sid, create_date, modify_date
FROM sys.sql_logins
WHERE [proper noun] = 'sa'
Get
To address the risks of irresolute 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 fourth dimension the sa countersign was inverse, then information technology is worth taking a look at your key SQL Servers
- If the final fourth dimension the sa password has changed is unacceptable to you, then begin to work with your squad on taking the necessary steps to changing the sa countersign
- In add-on, stay tuned for the upcoming sa serial 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 dried in the futurity, exist certain 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 starting time 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 observe out who and what applications are using the sa user proper name and password
- Countersign direction options for the sa login
- Cheque out the MSSQLTips.com security related tips
- Check out this tip on SQL Server 2005 DDL Auditing to rails password changes for SQL Server 2005
Related Articles
Popular Articles
Virtually the author
Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.
View all my tips
Article Concluding Updated: 2006-12-27
How to Find Last Login on Sql Server
DOWNLOAD HERE
Source: https://www.mssqltips.com/sqlservertip/1142/when-was-the-last-time-the-sql-server-sa-password-changed/
Posted by: 9newsonlinehhg.blogspot.com
comment 0 comments
more_vert