Create New Microsoft SQL Administrative Login

article #1160, updated 310 days ago

Often one will find a Microsoft SQL server with no working or available administrative login. To set one up:

  1. Stop the database. You’ll need its name. Go to services.msc , find entry “SQL Server (XYZ)”; XYZ is the name.
  1. Set up the database to run in single-user mode. Take the executable line in Services Manager for the SQL server, add “-m” to the end (no spaces, no quotes), and run it in a new administrative CMD. An example:
    "E:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sXYZ -m
  1. From another administrative command prompt, run the command-line client:
SQLCMD -E -S 127.0.0.1\XYZ

This may take multiple attempts. The attempts may error saying “Reason: Server is in single user mode. Only one administrator can connect at this time.” You may need to stop and restart the server. Sometimes the server just takes a while to reach functionality. Eventually the client will connect will connect and give you the prompt:

1>
  1. Enter the following, press Enter at every line, and choose something long and horrible for PasswordText:
CREATE LOGIN [sa2] WITH PASSWORD = 'PasswordText'
GO
GRANT CONTROL SERVER TO [sa2]
GO
EXIT
  1. Reverse the change in SQL Server Configuration Manager.
  1. Restart the SQL server.

Categories: