How To Enable SA Account In SQL Server

As an SQL Server DBA, you will encounter many scenarios in which enabling the SA (System Administrator) account is necessary. My daily activity is troubleshooting authentication issues and working on some Legacy applications that require sa access. Based on my analysis, I have identified some simple approaches to enabling an account in SQL Server. In this article, let me walk you through all the approaches individually.

Enable SA Account In SQL Server

Below are the approaches that you need to utilize for this purpose.

Approach-1: Using SQL Server Management Studio (SSMS)

Follow the below steps.

1. Launch SQL Server Management Studio (SSMS) and connect to your SQL instance.

2. Expand the Security folder and then expand the Logins folder, as shown in the screenshot below.

3. Right-click on the SA login and then select the properties option as shown below.

4. Enter the password and confirm the password in the General tab, then click on the Status tab from the top left, as shown in the screenshot below.

5. On the Status tab, select the Enabled option for Login and then click on the Ok button, as highlighted in the screenshot below.

Check out How To Create A User In SQL Server Database

Approach-2: Using SQL script

We can also execute an SQL script to enable the SA account, as mentioned below.

USE master;
GO
ALTER LOGIN sa ENABLE;
GO
ALTER LOGIN sa WITH PASSWORD = 'Rajkishore@123';
GO

The query was executed successfully, as shown in the screenshot below.

Now, we can cross-check if it is enabled using the SSMS; we can see that the red cross mark is not on top of the SA account, which means it is enabled. Refer to the screenshot below.

Alternatively, we can execute the below query; if is_disabled is 0, it is enabled.

SELECT name, is_disabled
FROM sys.server_principals
WHERE name = 'sa';

After executing the above query, you can see the is_disabled is 0, which means the account is enabled now. Refer to the screenshot below.

Conclusion

Enabling the SA account in SQL Server requires careful execution. As mentioned in this article, you can either use the SQL Server Management Studio (SSMS) design approach or an SQL query to enable this. This is crucial to maintain a secure database.

You may also like following the articles below.

• Grant Write Access To Table In SQL Server
• How To Get Table Count In SQL Server
• SQL Server Remove Duplicates From A Table