SQL Server 2008 database engine login failed for administrator user in windows 7

Question

I installed SQL Server 2008 Enterprise Edition on Windows 7 Ultimate from sadegh user.

This account exists in administrators role. after a few days I removed sadegh user from Windows and now I am using administrator user. But I can't login to SQL Server database engine using Windows authentication method and I receive this error message:

TITLE: Connect to Server

Cannot connect to SADEGH-PC.

ADDITIONAL INFORMATION:

Login failed for user 'Sadegh-PC\Administrator'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

please help me! thanks

Answer 1

Check http://techierg.blogspot.com/2010/05/login-issues-for-sa-user-in-sql-server.html and the links in that which may be helpful.

Answer 2

Depending on what mode SQL Server was installed (Windows Authentication or Mixed Mode), you may have to do a few things. If you installed in mixed mode, you can log in as sa and add the administrator user as a Login under the Security section. Make sure you assign sysadmin role to the user, which can be found under Server Roles.

If Windows Authentication mode was chosen during install then there are a couple of things you can do, but I think the easiest is just to recreate the user sadegh, login into SQL Server as that user, and follow the previous step to add the administrator user as a login.

UPDATE: If all else fails, you can run SQL Server in Single User Mode, which allows a single connection to the server, and enable/change the sa password. To do this you can:

1. Open the command prompt (Right-Click on and select "Run As Administrator")
2. From the command prompt type net stop MSSQLSERVER
3. Next type net start MSSQLSERVER /m
4. Open SQL Server Management Studio. Do not login, cancel the login dialog.
5. From the file Menu select New->Database engine query, and login (Make sure you use the host name and not localhost).
6. Execute the query ALTER LOGIN sa WITH PASSWORD = ''; to reset the password (if the sa is not enabled then type ALTER LOGIN sa ENABLE to do so)
7. Login with the sa user and add the Administrator user.

Tested on Windows 7 Ultimate with SQL Server 2008 Standard. Hope this helps.

Answer 3

In previous versions of SQL the BUILTIN\Administrators group was given the sysadmin role. Local administrators are not given login privileges by default in SQL 2008. Only the user performing the install is defaulted sa privileges. During install you have the option to add additional users, but you apparently did not.

You will need to login as sa and configure whatever windows logins are required (such as the local administrator account).