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
-mand then click Add
- Start SSMS and choose Run as administrator, and login with windows creds
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)
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
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