2

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?

ProfK
  • 483
  • 5
  • 9
  • 28

3 Answers3

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.

Chris W
  • 2,670
  • 1
  • 23
  • 32
  • 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... http://blog.sqlauthority.com/2009/08/04/sql-server-forgot-the-password-of-username-sa/ ... 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 01:01
0

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:

http://code.msdn.microsoft.com/addselftosqlsysadmin/

0

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:

sqlcmd -E -S HOSTNAME\INSTANCENAME

Or connect to via SSMS with:

\\.\pipe\hostname\instancename\sql\query

And use this to add your login and role:

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

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.