By Hally | Last Updated
SA account is disabled after we just install Microsoft SQL Server and choose Windows Authentication mode on computer. If we want to use SA account or connect SQL Server with Mixed Authentication mode, we have to enable SA account in SQL Server.
By changing SQL Server Authentication mode from Windows Authentication mode to SQL Server and Windows Authentication mode (sometimes called Mixed Mode Authentication), and choose SA account as SQL Server logon account. Then SA account will be enabled for SQL Server logon.
Step 1: Change SQL Server Authentication Mode.
1. Login into SQL Server with Windows Authentication mode.
Start SQL Server Management Studio, choose Windows Authentication mode, and click Connect button.
2. Change SQL Server properties.
Right-click Server in Object Explorer, and click Properties. In Server Properties window, select Security, and choose SQL Server and Windows Authentication Mode. Then click "OK" to save changes.
More about this, please turn to see the articles:
Step 2: Enable SA account logon.
1. After enabling SQL Server Mixed authentication mode, navigate to Security Logins sa to enable SA account in Object Explorer. Right click sa account and go to Login Properties, set password for sa account and confirm it. Then select Status option, set sa account Enabled.
2. Click "OK" to save changes and close Login Properties.
Restart SQL Server Services and SQL Server Management Studio. And you will be able to login into SQL Server with sa account under SQL Server Authentication mode.
1. Login into SQL Server using Windows Authentication mode and open Management Studio.
2. Click "New Query" to create a query, and type followings:
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD='password'
GO
ALTER LOGIN [sa] ENABLE
GO
3. Execute the commands in query. SA account would be enabled and set with password for SQL Server logon.
But sometimes, you would not successfully run the commands, and get errors:
'Msg 15151, Level 16, State 1, Line 1
Cannot alter the login 'sa', because it does not exist or you do not have permission.'
This error occurred because of insufficient rights.
SA is system admin user and it is the highest level of user in system. If any user have to modify the permissions of SA, that user needs to have higher or equivalent rights as SA user. Users member of systemadmin group are can only change the rights of SA user.
Solution: First adding any user to systemadmin role and then using the same user to modify system admin's tool will fix the issue.
Related Articles:
