Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

IT recently re-setup my laptop with 64bit windows. He installed Sql Server dev edition for me and set it up with a username and password. I logged in and tried changing Sql Server to windows authentication, restarted sql server, and now it won't let me log in with my domain account.

I have tried adding myself to the SQLServerSQLAgentUser and SQLServerMSSQLUser groups on my local system, but Sql Server still keeps saying my domain account is not authorized. My account is a local system administrator.

How can I get Sql Server to let me log in?

share|improve this question
You should post the exact error message. – Ek0nomik Mar 5 '12 at 16:10
The error message is just "Login failed for user "DOMAIN\user"" – KallDrexx Mar 5 '12 at 16:10
What version of SQL Server are you using? – Ek0nomik Mar 5 '12 at 16:19
sql server 2008 R2 – KallDrexx Mar 5 '12 at 16:26
I asked because before 2008 I the local administrator account was granted access to databases by default. But, that behavior changed in 2008 ( – Ek0nomik Mar 5 '12 at 16:31
up vote 2 down vote accepted

Unfortunately I think you'll either have to:

  1. Get your local and beloved IT friend to log in to the database and set up your account correctly.
  2. Reinstall SQL Server under your account so that you are automatically granted log in permissions.

If you don't have access to the database, then you can't update the security to give yourself permissions.

In order to log in to your SQL Server (individual database permissions may still be missing) the security login needs to be created with Windows authentication selected and then the domain account name is the Login name.

enter image description here

share|improve this answer
I figured this was the case. I went to IT and got the local admin user he set sql server up with, reset the sa password. I was thinking that since I was a local admin I could set it up but I guess not. Can't figure out how to get it to recognize my domain account for windows auth though. – KallDrexx Mar 5 '12 at 16:26
You're looking at the server logins correct, and not the database logins? I added a picture to show where you should be configuring the server login. – Ek0nomik Mar 5 '12 at 16:35
Ah I was looking at the server properties, not the new login screen! Thanks! – KallDrexx Mar 5 '12 at 16:41

And also, since you are a local administrator.

follow microsoft step by step instructions: or as below:

Stop the SQL Server Service.

Open a command prompt as administrator and browse to the SQL Server binn folder (C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn by default)

start the SQL Server in single user mode using the console output

sqlservr.exe -c -m

Then start SSMS or SQLCMD as admin, connect and run the following query:



EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\USER', @rolename = N'sysadmin'


share|improve this answer
Shawn's answer didn't work for me. This one did. +1 – Antony Koch Jul 6 '15 at 15:01
Correct answer ! – Warrior Prince Sep 4 '15 at 18:52

Since you are a local adminstrator you can use the backdoor to SQL Server instance to obtain access.

Download psexec.exe (PSTools from TechNet here).

Go to a command prompt and to the directory where you extracted the file (psexec.exe). Run the command psexec.exe -i -s ssms.exe or psexec.exe -i -s sqlwb.exe for SQL 2005.

This is going to open SSMS as the NT AUTHORITY\SYSTEM account, which by default is given SYSADMIN rights to the instance. You should then be able to add your login to the instance. If this does not work it is likely the system account permission was removed.

NOTE: If you are on Vista or Windows 7 you will probably need to run the command prompt as Administrator (right-click "run as Administrator").

Second Note: This will not work on SQL Server 2012 or above because the built-in administrator group is no longer added as sys admin by default. The exception to this could be if you are eating with an in-place upgrade.

share|improve this answer
This is bad ass! How has no one voted you up before?! @KallDrexx you really should mark this as the answer. – Chris Mar 22 '13 at 15:38
@Chris is right, this works really well! – CLJ Apr 6 '13 at 21:31
NT AUTHORITY\SYSTEM works well, if you are on 2008R2 or lower. The default install in 2012 and higher does not give the system account access to the database engine. – Spörri Apr 14 at 17:01

Should you have ensured that your windows account had sufficient permissions to log in before you changed it to Windows Auth?

share|improve this answer
yeah I should have but I thought that all local admins would have access so I didn't think about it. – KallDrexx Mar 5 '12 at 16:10

Your Answer


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

Not the answer you're looking for? Browse other questions tagged or ask your own question.