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);

SQLCMD -L “List instances it can see”

Reset the service with the -m or -m; switch

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

 

References

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 *

ten − nine =