Windows OS Hub
  • Windows Server
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Group Policies
  • Windows Clients
    • Windows 10
    • Windows 8
    • Windows 7
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
  • PowerShell
  • Exchange
  • Home
  • About

Windows OS Hub

  • Windows Server
    • Windows Server 2016
    • Windows Server 2012 R2
    • Windows Server 2012
    • Windows Server 2008 R2
    • SCCM
  • Active Directory
    • Group Policies
  • Windows Clients
    • Windows 10
    • Windows 8
    • Windows 7
    • MS Office
    • Outlook
  • Virtualization
    • VMWare
    • Hyper-V
  • PowerShell
  • Exchange

 Windows OS Hub / SQL Server / How to Reset a SA Password on MS SQL Server 2012

August 19, 2015 SQL Server

How to Reset a SA Password on MS SQL Server 2012

In this article we’ll describe the procedure of  restoring access to a MS SQL Server 2012 database if the the SA password has been forgotten or there are no users with administrative privileges to manage MS SQL 2012. We’ll show how to reset the password of the SA account, and if necessary, to give another Windows account MSSQL administrator privileges.

It often happens that SA password to a MS SQL database is lost or forgotten. As a rule, SQL reinstallation or database reconnection are not considered, since in this case the configuration settings stored in the master system database will be lost. SQL Server offers a more convenient way to restore access to the database. The matter is that the members of the local administrator group in the system with SQL Server have full privileges for the database run in a single-user mode. Running SQL Server in a single-user mode, you can change the SA account password, and/or give administrative privileges to any Windows account.

Tip. We remind that since SQL Server 2008 the local administrator group in the system with SQL Server is not added to sysadmin role on the SQL server by default.

You can start SQL Server 2012 in single-user mode and restore access to it as follows: (The main requirement is to have administrative privileges in the system with MSSQL installed.)

  1. Stop the service of the necessary SQL instance (in our case the name of this MSSQL instance is EPOSERVER) : net stop MSSQL$EPOSERVER
  2. Open the Registry Editor (regedit.exe) and go to the SQL start options section: HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\ MSSQL$EPOSERVER
  3. Change ImagePath value by adding -m key as an argument of sqlservr.exe. In our example, we have got this value: "C:\Program Files\Microsoft SQL Server\MSSQL11.EPOSERVER\MSSQL\Binn\sqlservr.exe” -m -s sEPOSERVERMSSQL ImagePath - single mode
  4. Run SQL Server: net start MSSQL$EPOSERVER
  5. Now SQL is run in the single-user mode and allows any member of the local administrator group to join the SQL Server instance with sysadmin rights.
  6. Using Management Studio, change the SA password or give the sysadmin role to any Windows account. This can be done from the sqlcmd command prompt: EXEC sp_addsrvrolemember 'DOMAIN\Username', 'sysadmin';
  7. Now you only have to stop SQL service and change ImagePart value to the original one by removing “-m” attribute (“C:\Program Files\Microsoft SQL Server\MSSQLEPOSERVER\MSSQL\Binn\sqlservr.exe" -s sEPOSERVER) and then run SQL Server in the normal mode
Tip. When writing this article, I found out that SQL Server 2012 can also be run in the single-user mode from SQL Server Configuration Manager. To do it, you need to add “-m;” key to Startup Parameters of the necessary instance in Advanced tab.

SQL Server Startup Parameters
Then follow the steps described above.

0 comment
0
Facebook Twitter Google + Pinterest
previous post
Exchange 2013: MAPI over HTTP
next post
Kerberos Token Size and Issues of Its Growth

Related Reading

MS SQL Server 2019 Installation Guide: Basic Settings...

January 19, 2021

Querying Microsoft SQL Server (MSSQL) Database with PowerShell

November 18, 2020

FAQ on Microsoft SQL Server Licensing

October 7, 2020

SQL Server Install Error: Could Not Find the...

September 30, 2019

How to Obtain SeDebugPrivilege when Debug Program Policy...

December 28, 2017

Leave a Comment Cancel Reply

Categories

  • Active Directory
  • Group Policies
  • Exchange
  • Windows 10
  • Windows 8
  • Windows 7
  • Windows Server 2016
  • Windows Server 2012 R2
  • Windows Server 2008 R2
  • PowerShell
  • VMWare
  • MS Office

Recent Posts

  • How to Configure and Connect an iSCSI Disk on Windows Server?

    January 26, 2021
  • Preparing Windows for Adobe Flash End of Life on December 31, 2020

    January 22, 2021
  • Checking User Logon History in Active Directory Domain with PowerShell

    January 22, 2021
  • How to Disable/Remove Thumbs.db File on Network Folders in Windows?

    January 21, 2021
  • MS SQL Server 2019 Installation Guide: Basic Settings and Recommendations

    January 19, 2021
  • USB Device Passthrough (Redirect) to Hyper-V Virtual Machine

    January 15, 2021
  • Windows 10: No Internet Connection After Connecting to VPN Server

    January 13, 2021
  • Updating the PowerShell Version on Windows

    December 24, 2020
  • How to Enable and Configure User Disk Quotas in Windows?

    December 23, 2020
  • Restoring Deleted Active Directory Objects/Users

    December 21, 2020

Follow us

woshub.com
  • Facebook
  • Twitter
  • RSS
Popular Posts
Footer Logo

@2014 - 2018 - Windows OS Hub. All about operating systems for sysadmins


Back To Top