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

Advertisements
Problems creating databases in SQL Server?

Azure: Stuck at “Resolving DNS”?

… I simply cannot believe sometimes it appears that the infamous update for a particular service doesn’t make it all the way back to the client.

 

What does this mean? Well, I created a new storage account, #20 of 20. I know that is a large number, but bear with me.  🙂

The other 19 were just fine, but for some reason #20 just didn’t want to finish ticking over. I’d been waiting… and waiting… and waiting… waiting for that durned “Resolving DNS” notice to go away… and it never did. Not until I wised up and decided to use another browser. Imagine my horror when I realized that I’d been waiting on the browser cache to clear, because my alternative browser (read NOT IE 10) had no cache for this site. When I closed IE10 and opened it up again…? Yep, I wanted to go throw up.

 

<<>>

Azure: Stuck at “Resolving DNS”?