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

The sa account got locked out and even though mixed mode authentication is enabled. When I select "Windows Authentication" the Username and Password fields are greyed out. The username field contains my current windows logon and the password field is blank. I am logged in with a windows administrator account though I hadnt explicitly added this account to the SQL server sysadmin role.

I have also tried "Run as Administrator" with the SQL server management studio but the problem persists

Please note that SQL server was installed with another windows administrator account. Could this be the reason ? Will it work if I login with the account with which SQL server was installed ?

Thanks

share|improve this question

migrated from stackoverflow.com May 12 '11 at 18:28

This question came from our site for professional and enthusiast programmers.

    
"Will it work if I login with the account with which SQL Server was installed." > What is the worse that could happen if you tried? Try it first! The reason why the windows logon is greyed out is because it is using windows authentication and it uses your current NT credentials. –  JonH May 12 '11 at 16:59

3 Answers 3

Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out:

Resolution

Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

To start the SQL with -m (from a console running as administrator):

net stop mssqlserver
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m

Or, if you have a named instance:

net stop mssql$<INSTANCENAME>
cd "c:\program files\Microsoft SQL Server\...\MSSQL\binn"
sqlservr -c -m -s <INSTANCENAME>

There are alternative means as shown in How to: Configure Server Startup Options, but I always preffer start from command prompt for something like this. After you start the instance with -m, connect with SSMS 'Run as Administrator` and add yuorself to the SQL sysadmin group:

create login [domain\you] from windows;
sp_addsrvrolemember 'domain\you', 'sysadmin';

Now shutdown your SQL Server and restart it normally (start back the service). domain\you is now a sysadmin on the server and can administer it. You don't need to run SSMS as administrator.

share|improve this answer

You can set the mixed mode of SQL Server (if you only activated SQL Server authentication) via the registry:

Default instance:

"HKEY_LOCAL_MACHINE\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode"

to 2 for mixed-mode or 1 for integrated.

After restarting the service you should be able to logon with a local administrator and reactivate the SA account again.

Another solution is use the tool - SQL Server Password Changer. It can reset your sa password as well as unlock your account in case it was locked out or disabled.

share|improve this answer

You'll probably need to put it in single-user mode (Unlike in earlier versions, local Windows admins aren't automatically granted access to SQL Server 2008): http://blogs.ameriteach.com/chris-randall/2009/12/11/sql-server-2008-forgot-to-add-an-administrator-account.html

share|improve this answer

Your Answer

 
discard

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