Change SA Password in SQL Server & Identify when last time SQL Server SA Password was changed? How to enable SA account in SQL Server?

Introduction

In this article we will take a look at how to change SA Password in SQL Server using TSQL code and by using SQL Server Management Studio. The steps mentioned in this article are applicable to change any SQL Server Login Password works on SQL Server 2005 and higher versions.

When Last time SQL Server SA Password was changed?

Execute the below TSQL script to know when last time SQL Server SA Password was changed.

MORE SQL SERVER TROUBLESHOOTING ARTICLES

ALSO READ ... STEPS TO REBUILD SYSTEM DATABASES IN SQL SERVER
DETECT VIRTUAL LOG FILES IN SQL SERVER TRANSACTION LOG

Sign up today for MyTechMantra.com Newsletter
Like Us on FACEBOOK   |  Follow Us ON TWITTER

USE Master
GO

SELECT   
    name   AS [Login Name]
  , sid   AS [SID]
  , type_desc  AS [Login Type] 
  , create_date  AS [Created Date]
  , modify_date  AS [Last Modified Date]
FROM    sys.sql_logins
 WHERE   [name] = 'sa'
GO   

Different ways to change SA Password in SQL Server

• Change SA Password in SQL Server Using TSQL Query
• Change SA Password in SQL Server Using SQL Server Management Studio

---

Change SA Password in SQL Server Using TSQL Query

USE master
GO

ALTER LOGIN [sa] WITH PASSWORD=N'NewSAPassword'
GO

You will receive the below mentioned error message as the password specified does not meet windows policy requirements because it is not complex enough.

Error Message

Msg 15118, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because it is not complex enough.

It is always a best practice to set a complex password for an SA (System Administrative Account) in SQL Server to avoid unauthorized access. If you still want to set a simple password for an SA account in SQL Server which is not recommended; then you add CHECK_POLICY = OFF clause to the above query.

USE master
GO

ALTER LOGIN [sa] WITH PASSWORD=N'NewSAPassword', CHECK_POLICY = OFF
GO

There may be a scenario that once you try logging in to SQL Server using SA Password you get the below mentioned error.

Error Message

Login failed for user 'sa'. Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)

Enable SA Account in SQL Server

In such a scenario you need to go ahead and run the below TSQL code to enable SA account in SQL Server.

USE master
GO

ALTER LOGIN [sa] ENABLE
GO

---

Change SA Password in SQL Server Using SQL Server Management Studio

1. Connect to SQL Server Using SQL Server Management Studio
2. Expand Security -> Logins -> Right click sa to open up Login Properties as shown in the snippet below.

In Select a Page choose Status page and on the right side pane choose the Enabled radio button for Login to allow login for SA password as shown in the below snippet.

Important Note Once SA Password has changed successfully you can login immediately with the new Password without restating SQL Server. In order for SQL Server Authentication to work you need to change Server Authentication as SQL Server and Windows Authentication Mode if not already selected. For more information on this refer the second page of the article titled “How to Identify Currently Used SQL Server Authentication Mode”.

Conclusion

In this article you have seen how easily you can change SQL Server SA Password using TSQL Code and by using SQL Server Management Studio.