DataGinger.com
Home | Pages | Archives
SQL Server – Why is the ‘sa’ Login Account Disabled & How to Enable ‘sa’ Login Account
August 20, 2013 8:30 am
Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state?
The reason is simple, sa login account is disabled out of the box (by default) in Windows Authentication mode. You have to enable manually to use it.
On the other hand, if you request Mixed Mode Authentication during installation, SQL Server Setup prompts you to set an sa login password. So sa login account is enabled in this case.
Enable/Disable sa login using SSMS GUI:
- From the Object Explorer, expand “Security” and then expand “Logins” –> Right click on sa and select “Properties”
- On the “General” tab, create a strong password
- On the “Status” tab, click “Enabled”, and then click “ok” (If sa is already enabled, you have to chose “Disabled” to disable the same)
Enable sa login using T-SQL script:
ALTER LOGIN sa ENABLE ;
GO
ALTER LOGIN sa WITH PASSWORD = '$trongPa$$w@rD';
GO
Disable sa login using T-SQL script:
ALTER LOGIN sa DISABLE ;
GO
Here’s some more info about “sa” account:
- System administrator (sa) is a special login provided for backward compatibility
- Usually there is no effect sa being in a disabled state though it pertains and owns the system databases
- By default, sa login is assigned to the sysadmin fixed server role and cannot be changed
- Microsoft do not recemmond using sa login in application (as it is often targeted by malicious users)
- However, Microsoft recommonds using sa only when there is no other way to log in to an instance
- The sa login cannot be removed/deleted
Technical Reviewer: Jaipal Vajrala
Posted by Suresh Raavi
Categories: Security, T-SQL
Tags: SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012
« Older Newer »
Leave a Reply
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
Enable sa login using T-SQL script: helped me. Thank you.
By N T Sukumar on October 18, 2014 at 5:25 am
it’s not working ……
By roushann8 on January 6, 2015 at 6:17 am
You also need to enable ‘SQL Server and Windows Authentication mode’ under Server Properties
By Skeet on February 24, 2015 at 8:06 pm
[…] For more information on sa account you can check my previous blog post HERE […]
By SQL Server – Change Ownership for All User Databases to “sa” Account | SqlServerZest.com on June 5, 2015 at 4:28 pm
[…] SQL Server – Why is the ‘sa’ Login Account Disabled & How … – Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state? The reason is simple, sa login account is disabled out … […]
By How To Enable Sa Account In Sql Server 2008 | People Life on June 26, 2016 at 11:02 am
This article worked perfectly for me. I didn’t have SSMS installed on the pc, so I put the T-SQL script statements into a sql file, set the correct password, and ran it from the command prompt like so:
>Sqlcmd -S.\SQLEXPRESS -i c:\temp\sqlfile.sql
… this successfully enabled the sa account.
Many thanks
By Kylie Cram on July 29, 2019 at 3:24 pm
[…] SQL Server – Why is the ‘sa’ Login Account Disabled & How … […]
By sql server sa login log Online Portal Client Support Activation Password Information - banklogining.com on October 30, 2021 at 4:48 pm
[…] SQL Server – Why is the ‘sa’ Login Account Disabled & How … […]
By test sql sa com Login Sign In Account Page Guide http - trustba.com on December 17, 2021 at 8:48 am
[…] SQL Server – Why is the ‘sa’ Iniciar Sesion Account Disabled & How … […]
By ➤ Iniciar Sesion Failed For User Sa The Account Is Disabled 【Iniciar Sesion】 on March 9, 2022 at 10:26 pm
[…] » Visit Now Aug 20, 2013 · SQL Server – Why is the ‘sa’ Login Account Disabled & How to Enable ‘sa’ Login Account August 20, 2013 by Suresh Raavi Ever noticed and wondered why the well-known SQL Server system administrator ( sa ) login is in a disabled state? […]
By Login Failed For User Sa The Account Is Disabled Sign In - logininfos.com on July 12, 2022 at 12:31 pm
[…] » Visit Now Aug 20, 2013 · SQL Server – Why is the ‘sa’ Login Account Disabled & How to Enable ‘sa’ Login Account. August 20, 2013 by Suresh Raavi. Ever noticed and wondered why the well-known SQL Server system administrator (sa) login is in a disabled state? The reason is simple, sa login account is disabled out of the box (by default) in Windows … […]
By Sql Server Sa Login Thread - logininfos.com on August 10, 2022 at 9:28 pm