There are a bunch of blogs out there that show how to do it, but I always seem to have problems.  Anyway, here is my last attempt, and what I did to fix it (It is also case sensative)(DO NOT copy paste, type these out manually);

 

Below is older way, newer way is here, and first link below;

  • Start SQL Server Configuration Manager and choose Run as administrator
  • On the Startup Parameters tab, in the Specify a startup parameter box, type -m and then click Add
  • Restart
  • Start SSMS and choose Run as administrator, and login with windows creds

Old way

First set SQL server to Single user Mode, add the -m switch to the SQL service, and restart in the SQL ServerConf Manager.

SQLCMD -L “List instances it can see” SQl Agent must be started to see

sqlcmd -S Servername -U username -P password “to connect” (User and Password may not be needed, but i include it here so I know the switches in case it does)

Commands

CREATE LOGIN tom WITH PASSWORD='P@ssword123'
GO
SP_ADDSRVROLEMEMBER tom,'SYSADMIN'
GO

or for 2012+

CREATE LOGIN tom WITH PASSWORD='P@ssword123'
GO
ALTER SERVER ROLE sysadmin ADD MEMBER tom
GO

Known Errors

C:\>SQLCMD -S SERVER\INSTANCE
Sqlcmd: ‘-S’: Unknown Option. Enter ‘-?’ for help.

C:\>SQLCMD -E -S SERVER\INSTANCE
Sqlcmd: ‘-S’: Unexpected argument. Enter ‘-?’ for help.

Invisible characters in copy paste from web

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user ‘
DOYONUTILITIES\itm4’. Reason: Server is in single user mode. Only one administra
tor can connect at this time..

Newer version, needs SQL Server services need to be RUNAS admin and then SSMS RUNAS also

References

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15

https://www.top-password.com/knowledge/reset-sql-server-password-in-single-user-mode.html

https://stackoverflow.com/questions/1528378/s-unknown-option-when-using-sqlcmd


Leave Your Comment

Your email address will not be published. Required fields are marked *

4 × 2 =