Problems creating databases in SQL Server?

“CREATE DATABASE permission denied in database ‘master’. (.Net SqlClient Data Provider)”
Oh SQL Server Express, how I hate you sometimes. Not only did you not let me specify an sa password when I installed you, but you didn’t take the time to make me a sysadmin either.

The worst part was that I got this when I was trying to get to a geek party an hour away from my house. Oh well. Part of the problem was that there was no clear answer (with example!) of how to get around this issue… so… here you go.  😉

Prerequisites for this technique:

  1. You have to be a local administrator on the box that has the SQL Server instance you’re trying add a database to.
  2. You have to be able to change the settings on Services on that box.
  3. You have to be able to start and stop Services on that box.

The technique:

  1. Go to Services and Stop the service that is the target SQL Server instance. Also stop any SQL Server Agent service which are tied to that SQL Server instance service.
  2. Go to the Properties of the target SQL Server Instance service.
  3. In the Start Parameters field, add “;-m” to the *end* of whatever is already there. DON’T  put in spaces OR include the quotes, and if nothing’s already there then you don’t need the semi-colon.
  4. Click OK and start the target SQL Service Instance service.
  5. Do a Windows Search for sqlcmd.exe … don’t be alarmed if there are several. You should use the one in the path that matches the SQL Server version that the target SQL Server Instance.
  6. Open an elevated command prompt.
  7. Path to the folder that has the sqlcmd executable in it. In my case, this was C:\Program Files\Microsoft SQL Server\110\Tools\Binn>.
  8. Run the following, with the appropriate user info, and DO include the quotes:
    sqlcmd -S MachineName\SQLServerInstance  -e -Q “EXEC sp_addsrvrolemember ‘UserDomain\UserName‘, ‘sysadmin’;”
  9. Go back to the service, remove the “;-m” you added, then restart the service.
  10. Verify that the user now has the sysadmin Server Role (via SSMS or however you want to do this).
  11. Don’t forget to restart any SQL Server Agent services that were stopped in #1 above.
  12. Enjoy.  🙂

Special thanks to Raul Garcia for putting me on the right path. You can see his original post here: http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Problems creating databases in SQL Server?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s