SQL 2008 recover an account to the sysadmin role

[HMCTech]

Unfortunatly SQL was installed in windows authentication only and it is unknown which user has the sysadmin role. We have tried a few steps to add a user to the role, one of them being start in single user mode which we have done but when 'sqlcmd' is started it says an administrator is already connected.

 

As no account has the sysadmin role we can not change to windows and sql authentication which means the sa account is disabled.

[jamesb]

How to verify and change the system administrator password in MSDE or SQL Server 2005 Express Edition

 

I'm not sure if this will also apply to SQL 2008, but you can always have a look. The KB above mentions a registry key which can be set to change the authentication mode to Mixed.

 

HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

 

If that works you should be able to go in as sa and move on from there.

 

It is actually possible with SQL 2008 to install without any administrators, since the default rights are not assigned as they were in SQL 2005. You have to explicitly state any accounts you want to give admin rights.

[jamesb]

As an alternative, if you've set up the SQL service to run under a dedicated account, could you use this to run a SQL query against the database to add an administrator/other account as serveradmin?

 

Edit: Or better yet - http://blogs.ameriteach.com/chris-randall/2009/12/11/sql-server-2008-forgot-to-add-an-administrator-account.html