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


Jun 9, 2012

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.

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			
When last time SQL Server SA Password was changed

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

Recommendation: - MyTechMantra.com recommends the use of performance monitoring tools to be run on the SQL Server to identify potential bottlenecks. Hence we recommend you to take a look at "Database Performance Analyzer" one such Free Tool to avoid issues proactively.

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.

Change SA Password In SQL Server Using SQL Server Management Studio

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.

Enable SA Login in SQL Server

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.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article


Geeks who read this article also read…




Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."