I had some trouble coming up with a sensible title for this one, so sorry if it doesn’t make any sense. Anywho, I had a awkward issue when I was migrating a database to a new server today…I didn’t have any administrative access over the SQL Server Express (2008) installation! Windows Authentication would not give me administrative access even though it should have (yes the server was configured to accept Windows Authentication and SQL Authentication, and I was a Domain Admin/Local Admin), and the existing sa password on the server was a mystery that was impossible to solve. So I took to the interwebs and came across this post on stackoverflow. The comment by “40-Love” in particular held the solution for me, which I will detail below:
- Close all SQL Server apps
- Open services and stop all SQL services
- Open the SQL Server service properties
- Enter “-m” in the start parameters box
- Open Command Prompt as administrator and do the following
osql -S localhost\SQLEXPRESS -E
CREATE LOGIN my_Login_here WITH PASSWORD = 'my_Password_here'
sp_addsrvrolemember 'my_Login_here', 'sysadmin'
- Stop the SQL Server service
- Remove the “-m” you added to the start parameters earlier
- Start the SQL Server service
Wah-lah! I now had an account on the SQL Server with sa access to do my business with.