Add the current admin user to SQL Server Express 2008

Question

I have managed (in 'eksperiments') to remove both my Windows users from my SQL Express instance. I am now logged in as windows admin, and have re-created the corresponding SQL login, but I cannot assign sysadmin rights. I get the error:

User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

If admin can't do this, should I start looking for a small animal to sacrifice?

Solution

It is possible that your Windows admin account doesn't have admin rights - it depends what you set up. You'll need to login with an account that does have rights, or, log in as the sa user.

If you don't have SQL authentication enabled you can activate it with a registry tweak. I think this is the right key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<<instance name>>\MSSQLServer\Loginmode

It should be set to mixed mode (2) but of course you still need to know what the sa password was when you installed the instance.

Comments

[Zach Saucier]

I have forgotten my sa password :-(

[Chris W]

In that case you'll need to force a reset. Pinal Dave is the saviour here... blog.sqlauthority.com/2009/08/04/… ... check the second option which should sort you out.

[Zach Saucier]

Ouch, I just re-installed - it wasn't too painfull, RS seems to be operating properly, unlike before. I didn't think of Option 1, but sort of Option Two was doing something wrong. But thanks.