sql server 2012

Recover SA Password on Microsoft SQL Server 2012


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 recover 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

mssqlserver

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;».

READ ALSO  Password Managers - What They're Good For And How to Use Them

eposerver

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

addsrvrolmember

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.

msq login properties

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.

READ ALSO  How to Recover Lost or Forgotten Password for MS Access

You may also like:

How to reset MySQL root password on Linux It’s a pretty common situation when you lost your MySQL root password. In earlier versions there was a possibility to open my.cnf file and get the pas...
Change Password in Office 365 via Powershell These are instructions on how to manually change a password for an account in 365 via Windows Powershell. This is the only way to set a password speci...
Changing expired password via RDS in Windows Serve... This article shows how the remote users can change their expired RDS password themselves through RDP-connection to the Remote Desktop Services (RDS) f...
How to Recover Lost or Forgotten Password for MS A... Hello guys! Today we are going to show you how to recover lost or forgotten password for MS Access database file (*.MDB). There are a lot of tools tha...