Those who utilize the Microsoft SQL Server database management system (typically within Windows Server) might sometimes need to change sa user passwords. Users can do that with the SQL Server Management Studio.
However, a Cannot alter the login `sa` error might arise when users try to change a sa password. As a result, users can’t change the passwords when that issue arises.
How can I fix the Cannot alter the login ‘sa’ error?
1. Log in as administrator
- Users usually can’t change the sa password because they don’t have sufficient admin permissions to do so. First, you’ll need to log out of Windows.
- Then log back into Windows with an admin account added to SQL Server.
- Launch the Microsoft SQL Server Management Studio software.
- Select Windows Authentication on the Authentication drop-down menu.
- Press the Connect button.
- Click Security > Logins > sa on the left of the Server Management Studio window.
- Click Status on the Login Properties – sa window.
- Deselect the Login is locked out checkbox if selected.
- Then click the General tab on the window to reset the password.
2. Modify the password with OSQL
- If you still can’t change a sa password, try changing the password with the OSQL SQL server tool. First, log in to your Windows virtual private server.
- Open the SQL Server Configuration Manager, which will usually be on the Start menu. However, you can also open it from its default WindowsSysWOW64 folder.
- Select SQL Server Services on the left of the window.
- Right-click SQL Server to select its Properties option.
- Select the Startup Parameters tab on the SQL Server window that opens.
- Then enter the following in startup parameter text box: -m;.
- Press the Add button, and select the OK option.
- A dialog box will then open. Click OK on that dialog box.
- Click OK on the SQL Server Properties window.
- Next, right-click SQL Server to select the Restart option.
- Open Run with the Windows key + R hotkey.
- Enter ‘cmd’ in Run’s text box, and press the Ctrl + Shift + Enter hotkey.
- Then enter the query below in the Command Prompt. Replace the <SQL SERVER INSTANCE> with your SQL server name and <password> with your own password:
osql -E -S .<SQL SERVER INSTANCE>
exec sp_password @new='<password>’, @loginame=’sa’
alter login sa enable
- Return to the SQL Server Configuration Manager window.
- Right-click SQL Server to select Properties again.
- Select –m; in the Existing parameters box on the Startup parameters tab.
- Press the Remove button.
- Click the Apply option, and press the OK button on the dialog box.
- Click OK to exit the properties window.
- Thereafter, you’ll need to restart the SQL server again. Right-click SQL Server on the Server Configuration Manager window to select Restart.
- Log in to SQL Server Management Studio with the new password you entered within the OSQL Command Prompt query.
Those are two ways you can fix the Cannot alter the login ‘sa’ error. First, try changing password with the Microsoft SQL Server Management Studio (as admin) and then OSQL.
If you have any other questions or suggestions, leave them in the comments section below and we’ll surely check them out.
RELATED ARTICLES TO CHECK OUT:
- SQL Server query execution failed for dataset [EXPERT FIX]
- Here’s how to fix high CPU usage by SQL server
- Operating system is not supported by SQL Server error [EXPERT FIX]