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?


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.

| improve this answer | |
  • I have forgotten my sa password :-( – ProfK May 30 '10 at 15:50
  • 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. – Chris W May 30 '10 at 19:38
  • 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. – ProfK May 31 '10 at 1:01

There's a script on CodePlex that automates recovering from losing sysadmin rights to your local SQL Server instance, provided you have access to a Windows admin account:


| improve this answer | |

Update the SQL Server Instance's service's start permissions to include '-m' or -m"Microsoft SQL Server Management Studio - Query" if you'd prefer to use SSMS over sqlcmd/osql.

Then use this command to connect via a command line:


Or connect to via SSMS with:


And use this to add your login and role:

CREATE LOGIN [domain\username] FROM WINDOWS;
EXEC sp_addsrvrolemember 'domain\username', 'sysadmin';

After you can remove the '-m' options on the service and start it normally.

See http://msdn.microsoft.com/en-us/library/ms188236.aspx for details.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.