Monday, December 13, 2010

SQL Express 2008 R2 - CREATE DATABASE permission denied in database 'master'

I think I figured it out.  I'm now having a problem importing data (more on that later), but at least I can create a database.  Here's what I did:

1.  shut down SQL Server from services

2.  open cmd window (as admin) and run single-user mode as local admin with this command:

"c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\ sqlservr.exe" -m -s SQLEXPRESS

3.  open another cmd window (as admin)

4.  open sqlcmd:

sqlcmd -S .\SQLEXPRESS

Now add the sysadmin user:

a.  sp_addsrvrolemember 'domain\user', 'sysadmin'

b.  GO

5.  now Ctrl+C the single-user mode from the first cmd window to kill SQL Server.  Now restart it from services the normal way.  Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

Before this, I was getting the error "Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)."  What the error utterly fails to tell you is that any connection to single-user mode qualifies as an administrator connection.  In effect, simply opening Management Studio in single-user mode was causing an administrator to connect.  The only way to do anything in single-user mode (it appears) is to do it thru sqlcmd.


No comments: