Archive

Posts Tagged ‘SQL Server’

How to recover sysadmin access to SQL Server 2012 when sa password is lost

January 12, 2017 Leave a comment
  1. Open the SQL Server Configuration Manager tool
  2. Navigate to SQL Server Services and Stop the SQL Server Instance that needs a sa password reset
  3. Right click the on the instance/service and select properties.
  4. Go to the “Startup Parameters” tab, and in the Specify a Startup Parameter text box write  “-mSQLCMD”, click Add and then click OK.
  5. Restart the SQL Server Instance  which will now be running in single-user mode

When the SQL Server Instance starts in single-user mode,  you can use the Windows Administrator account to connect to SQL Server using the sqlcmd utility and Windows authentication.

Connect to SQL Server using sqlcmd: sqlcmd –S.\SQLExpress

The following example adds the account “John” in the “EXAMPLE” domain to the SQL Server “sysadmin” role:

EXEC sp_addsrvrolemember ‘EXAMPLE\John, ‘sysadmin’;
GO

Note!
Once the sysadmin access has been recovered, remove the “;-mSQLCMD” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

-Eric

Advertisements
Categories: Commands, Microsoft, SQL Tags: ,

How to recover sysadmin access to SQL Server 2005/2008 R2 when sa password is lost

May 9, 2016 Leave a comment

 

  1. Open the SQL Server Configuration Manager tool
  2. Navigate to SQL Server Services and Stop the SQL Server Instance that needs a sa password reset
  3. Right click the on the instance/service and select properties.
  4. Go to the “Advanced” tab, and in the Properties text box add “;–m” to the end of the list in the “Startup parameters” option

    Note! – Please make sure there is no space between “;” and “-m”. In the SQL Server ERRORLOG, there should be an entry that says   “SQL Server started in single-user mode.”

  5. Click the “OK” button and restart the SQL Server Instance

When the SQL Server Instance starts in single-user mode,  you can use the Windows Administrator account to connect to SQL Server using the sqlcmd utility and Windows authentication.

Connect to SQL Server using sqlcmd: sqlcmd –S SERVER1\SQLExpress

The following example adds the account “John” in the “EXAMPLE” domain to the SQL Server “sysadmin” role:

EXEC sp_addsrvrolemember ‘EXAMPLE\John, ‘sysadmin’;
GO

Note!
Once the sysadmin access has been recovered, remove the “;-m” from the startup parameters using the Configuration Manager and restart the SQL Server Instance

-Eric