Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Situation: There is a SQL 2008 instance here that we would like access to. The person who setup the instance is no longer with the company and, apparently, did not set the instance up with the proper users as admins. However, the proper users are admins on the machine that is running the SQL instance.

Some informative links I've been able to dig up on the subject are included here for reference. All have been tried and the results are mentioned below. ('h' omitted per serverfault's rule against new users posting more than one hyperlink) [fixed by edit]

http://social.msdn.microsoft.com/forums/en-US/sqlsecurity/thread/81970e88-104d-4e89-ade8-746def18108e/

http://msdn.microsoft.com/en-us/library/dd207004.aspx

http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

When the disaster flag or single user flag is used to attempt to gain access the following error is still generated:

LOGIN FAILED FOR USER XXXX Error 18456

Any idea what the problem is with the solutions we're trying? If it matters the machine is on a totally different domain (across the world even) and attempts to login as the the service that we set to run the SQL instance (we have that password, btw) fail as well.

Thanks in advance for your time.

share|improve this question
    
EDIT: We're in using the service login and can look at things a bit closer now, but turns out the SA login is disabled, and of course we don't have the privilege level to enable it. The frustration continues. Attempted to enable the login via regedit but no luck flipping those bit either. –  SQL Problem Aug 30 '10 at 18:14

1 Answer 1

up vote 3 down vote accepted

Try enabling mixed mode by changing the authentication mode from Windows Registry by modifying the LoginMode subkey and then restart sql server service in single user mode using -m parameter. Then go to this folder using command prompt C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn and then try typing this SQLCMD -E and then execute these Tsql code to add windows administrators as login

use master;
go
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
go

now restart the service without -m option and change the SA password

good Luck

EDIT:

You might want to take a look at this article. it has screen shot and everything

share|improve this answer
    
Thanks for this! –  SQL Problem Aug 31 '10 at 14:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.