Often one will find a Microsoft SQL server with no working or available administrative login. To set one up:
- Stop the database. You’ll need its name. Go to services.msc , find entry “SQL Server (XYZ)”; XYZ is the name.
- Set up the database to run in single-user mode. Take the executable line in Services Manager for the SQL server, add “-m -f” 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 -f
- 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>
- 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
- Reverse the change in SQL Server Configuration Manager.
- Restart the SQL server.