Create New Microsoft SQL Administrative Login

article #1160, updated 407 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. In the Sql Server Configuration Manager, in SQL Server Services, in the properties of the desired SQL Server, in the Advanced tab, and for Startup Parameters, add “-m;” (no spaces, no quotes) to the beginning.
  1. Start the database.
  1. From administrative command prompt, run:
SQLCMD -E -S 127.0.0.1\XYZ

If you have only one SQL server on this machine, omit the -S and everything after.

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.” Eventually it 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: