I'm still haven't seen SQL Server Management Studio (SSMS) available to install through the Visual Studio 2010 installation. It's such an easy and painless way to interact with SQL, but it still isn't available as a separate download as far as I know. A big part of me is happy Microsoft finally realized that you have to give poor developers access to the same tools that the big boys use, but there are still places for future work.
I could reinstall SQL Server Express with the Advanced Tools, but I'm in a hurry and need to connect. To work around this, I'm attempting to connect using another machine with SSMS 2008 on it. Unfortunately, SSMS won't allow you to authenticate using a different computer's users (domain users wouldn't have this problem, I'm guessing). This leaves me with the option to login as the superuser SA.
So what do you do when you've been relying on Windows authentication and you need to reset the SA password?
- Fire up the SQL Server Configuration Manager and double-click on the SQL instance you need to reset. Click over to the Advanced tab.
- In the Startup Parameters, prepend -m;to the list to put SQL server into Single-User mode. Click OK and then right-click on the SQL Server instance and click Restart.
- Open up a command prompt, and type:
OSQL -S localhost -E
The -S parameter tells OSQL what server to connect to, and the -E parameter creates a trusted connection. Capitalization of the switches does matter. - Inside the SQL terminal, type (substituting your new password
for newPassword):
EXEC sp_password NULL, 'newPassword', 'sa'
GO - Once that's complete, type exit to quit. Go back to the SQL Configuration Manager, remove the -m; from the Startup Parameters, and restart the SQL Server instance.
- Make sure SQL Server and the browser are allowed through the Windows firewall, and try connecting from the other computer.
If it worked, you should now be able to log in using the sa user and make all the changes you need through the SSMS on the remote computer.