How to enable sa Account in SQL Server?


When you install the SQL Server using Windows Authentication mode, by default, the “sa” account is disabled. Sometimes, due to users/customers request, you may need to enable the sa account. You need to change the authentication mode for SQL server from Windows Authentication Mode to SQL Server and Windows Authentication Mode to use the sa account.

To Change the Authentication Mode:

Follow the steps mentioned below to change the authentication mode from Windows Authentication to SQL Server and Windows Authentication. You need to remember that, the SQL Server service needs to restart to make this change effective.

1. Login to the SQL server instance using SQL Server Management Studio. Right-click on the database instance, and go to Properties.

image

2. On the Server Properties page, Click on Security. Click on the radio button next to SQL Server and Windows Authentication mode, and click on OK to close the  Server Properties page.

image

3. Once you clicked on the Ok button, we will get the following screen:

image

As discussed earlier, we need to restart the SQL Server service to make this change effective. After restarting the SQL Server, the authentication mode will be changed to SQL Server and Windows Authentication mode.

Enable the sa Login:

1. Connect to the SQL Server instance using SSMS and go to Security. Expand Security, go to Logins.

2.  You can see the sa account is disabled when you install SQL Server using Windows Authentication mode.

image

3. Right-click on the sa account and go to Login Properties. Specify a complex password for the sa account. By default, the Enforce password policy is checked. (if you don’t want to provide a complex password for the sa account, you can uncheck this option. However, this is not recommended.)

image

4. Click on the Status page. By default, the sa account will be disabled. Click on the Enabled button to enable the sa account. Click on Ok to close the sa Login Properties.

image

Thus, sa account is enabled and you will be able to login to the SQL instance using the sa account.

If you want to use a script to enable the sa account, you can use the script mentioned below:

1: USE [master]
2: GO
3: ALTER LOGIN [sa] WITH PASSWORD=N'z43VGYT@Iu*60i'
4: GO
5: ALTER LOGIN [sa] ENABLE
6: GO
About these ads

About Sudeepta Ganguly

A SQL Server User... Still Learning
This entry was posted in Security in SQL Server. Bookmark the permalink.

33 Responses to How to enable sa Account in SQL Server?

  1. Kailash Yogi says:

    Thanks , It is very usefull for me.

  2. mkiara says:

    Thank you. It help me a lot. =)

  3. Steve Buck says:

    Thanks for the help. You gave the solution in an easy to follow format.

  4. aarthy says:

    hello

    I didn’t get the status page in sa login ….what can i do
    In properties it shows only General,server roles,database access

  5. driss says:

    thank you very much it resolves my problem

  6. Chintan Parekh says:

    Than You Sudeepta.

  7. Shovan Mukherjee says:

    The problem is solved i was facing, Thanks..nice post

  8. joseph says:

    it doesnt work for me because my accoun doesnt have the right permissions :( and there rest of the accounts are disabled, im talking about admin, sa, and local.

  9. nikhil says:

    thanks a lot save my day….

  10. Aditya says:

    It helped me and saved my time.. thanks a lot :)

  11. miran says:

    when i put the ok Button get the error message..
    An exception occurred while executing a transact-SQL statement or batch.
    Cannot set a credential for principal ‘sa’.(Microsoft SQL server, Error:15535)

    how to fix this error..please help me…..

  12. David Brossard says:

    Thanks a lot! I had found everything except for the last step – enable the user account. Thanks again.

  13. ko algee says:

    Thank you. That’s help a lot
    .

  14. George Millwood says:

    Thanks, it was a great help.

  15. Pingback: Enable SA Account in SQL Server | Chinthana Jayawardena

  16. Sanket says:

    Please let me know how to enable sa account and password during silent install of sql server 2005 express.

  17. Emdadul Sadik says:

    Thanx

  18. jo says:

    Thanks man, it very helpfull…

  19. dado says:

    works fine, thanx

  20. Joshit0 says:

    help me! I have a problem.
    I installed VisualStudio 2008 , and automatically also it installed SQL 2005 Express Edition. I can only join in Windows Autentication mode. The great problem is that I can’t do anything (can’t change to mixed mode, can´t enable sa user, can’t anything!).. I also try to change permissions by SQLCMD but neither.

    What can I do in this case?..

    I hope your answer..

    Thank you..

  21. MuthuKaruppan says:

    Yes superb. Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s