If you want to connect to Microsoft SQL Server, but the SA password is successfully forgotten and there is no user with the SQL Administrator rights — don’t worry! If you have local Administrator rights on the Windows operating system, you have the opportunity to get Administrative rights on the MS SQL Server and reset SA password.

How to Reset SA Password on MS SQL Server 2012?

As is known MS SQL supports 2 types of authentication: Windows Authentication and SQL Server Authentication. And the second one cannot be activated without the first. But starting from Microsoft SQL Server 2008, members of local Administrators group of a server, on which SQL Server is installed, by default is not added to the sysadmin role in SQL Server.

Run Command prompt with Administrator privileges and stop the MS SQL Server service using the following command:

net stop mssqlserver

With the help of key /m run the SQL Server service in single-user mode:

net start mssqlserver /m

Also you can start SQL Server 2012 in single user mode using the SQL Server Configuration Manager. To do this, open instance properties and click on the Advanced tab. In Startup Parameters option, you must add the argument «-m;».

In single-user mode any member of the local Administrators group have Administrator rights on SQL Server instance.

Run the utility sqlcmd (c:\windows\system32\sqlcmd.exe) with the current user permissions:

sqlcmd –E

If you need to connect to a named instance of SQL Server, then specify its name using the -S key. Like this:

sqlcmd -E -S <Sql_Server_Name><Sql_Instance_Name>

or in short form:

sqlcmd -E -S .<Sql_Instance_Name>

Now, add the user (a domain or local) to the list of SQL Server database users, for example:

CREATE LOGIN [WinSrvName\theitbros] FROM WINDOWS
GO

and assign SQL Server Administrator rights for that user:

EXEC sp_addsrvrolemember ‘WinSrvName\theitbros, ‘sysadmin’
GO

Complete the sqlcmd session (exit) and restart the SQL Server service in normal mode:

net stop mssqlserver

net start mssqlserver

You can then run the SQL Server Management Studio, connect to SQL Server using current credentials and reset the SA password. To do this, go to the section Security -> Logins, locate and double click on sa account. Reset current SA password on Login Properties page and click OK to save the changes.

Tip. Also you can change SA password from sqlcmd line:

ALTER LOGIN sa WITH PASSWORD = 'P@ssw0rd'

Similarly, you can get administrator rights on all supported versions of MS SQL Server, starting from 2008 and ending to 2014.

Source : https://theitbros.com/recover-sa-password-ms-sql-server-2012/