If the user forgets the SQL password, then the user can ask the SQL SA (system administrator). It is because the SQL SA can easily reset the user’s password. But, what need to do if the administrator forgot the password?
Here is how to Resolve the issue;
Step 01
Try to Login into SQL Server using Windows Authentication (always try start with Run as Administrator)
If you can log into the SSMS, check whether SQL Server and Windows Authentication mode is enabled under the SQL server properties.
Form there, you will be able to enable the SA login if its disabled.
Also, using the command line you will be able to enable the SA login
net stop MSSQLSERVER
net start MSSQLSERVER /m”SQLCMD”
Also, you will have the chance to change the SA login password as follows (after accessing the SSMS);
GO
ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master]
GO
USE [master]
GO
ALTER LOGIN [sa] WITH PASSWORD=N’NewPassw0rd’
GO
Step 02
Access SQL Server Configuration Manager and confirm that all the SQL services and Network services are running, as below.
SQL Services
SQL Network Services
Step 03
Open an elevated Command Prompt
Stop the SQL Instance. The default is MSSQLSERVER
net stop MSSQLSERVER
Start the SQL Instance in Single-User Mode
net start MSSQLSERVER /m”SQLCMD”
Get connected to the SQL instance
sqlcmd -SServerName\InstanceName
create a login called “TestUser” and give it the password “Password”
CREATE LOGIN TestUser WITH PASSWORD=’Password’
GO
Now, use more T-SQL commands to add the user to the SysAdmin role
SP_ADDSRVROLEMEMBER TestUser,’SYSADMIN’
GO
To exit SQLCMD, type ‘exit‘ and hit <ENTER>. Next, stop the SQL instance and then start it again without the ‘/m‘ switch so it is no longer in Single-User Mode
net stop MSSQLSERVER && net start MSSQLSERVER
Now you should be able to access the SSMS with the newly created credentials.