Add the current admin user to SQL Server Express 2008

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 1:

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.