4

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

1
  • "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, 2011 at 16:59

3 Answers 3

8

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:

  1. Open with SSMS using Run as Administrator
  2. At the first prompt don't click connect but say cancel
  3. Open a new query window and then connect to the database with that query window
  4. Now add yourself to the SQL sysadmin group

    create login [domain\you] from windows;

    exec 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.

0
2

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.

1

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

You must log in to answer this question.