SQL SERVER 2008 failed to log on (SQL and Windows do not have corresponding permissions)

Transferred from: http://www.cnblogs.com/zerocc/p/3425431.html

Yesterday in the test some of the permissions early this morning found that SQL SERVER landed, error: User Login failed: Msg 18456, Level 14, State 1, Server XXX, line 1th. User ' XXX ' login failed. My services and agents are automatically started, so the problem is not here, because it is a local service, there are no multiple accounts, the test when the SA was disabled by itself. Some users are part of the library's read-only permissions, how to do it?

The way is: Start SQL to single-user mode, and then log in with Sqlcmd-a, if you need to have the administrator rights of the computer. There are many ways on the internet is the same, but for the single-user mode of the description is not too detailed, at least for the small white like me still have a certain understanding of the problem, so write to facilitate the same as I mistake the results of the students to learn.

Enable local accounts:
1. First look at whether the account of this computer has administrator privileges, if not added on.
2. In the Start menu, enter CMD in the search box, right-click to select Run as Administrator
3. At the command prompt, enter NET stop Mssqlservre to stop MSSQLServer running (you can skip this step if you have stopped)
4. If 3 has a problem, prompt error, you can start-->sql Server--Configuration tool-->sql Server Configuration Manager--Open the SQL SERVER properties----high----
When adding parameters, be sure to add the last side of the startup parameter and add a semicolon so that it is separated from the previous. such as:-dd:\data\master.mdf;-ec:\program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\log\errorlog;-ld:\data\mastlog.ldf; m
5. If the above is not a problem, then switch to the installation path, that is, binn under the Sqlservr.exe path
such as: cd C:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\binn
6. Execute sqlservr.exe, that is, single-user mode entered the
7. Re-login with Administrator account to open a window, enter sqlcmd-a
8. Enter the action command you want to change, here I need to add a native account, such as:

Use mastergocreate LOGIN  [domain\username] from the  WINDOWS with Default_database=[master]goexec sp_ Addsrvrolemember @loginame =n ' domain\username ', @rolename =n ' sysadmin ' GO

To avoid errors, you can add an SQL account for a rainy-out or enable the SA command:

--Add user T1 and give administrator permission to use [Master]gocreate LOGIN [T1] with password=n ' [email protected] ', Default_database=[master], Check_ Expiration=off, Check_policy=offgoexec Master. sp_addsrvrolemember @loginame = n ' T1 ', @rolename = n ' sysadmin ' go--enable Saalter LOGIN [SA] Enablego

9. When the above operation is complete, close the 2 command-line windows and start SQL Server, which can be logged in.

Note: This environment is not tried for win7 + SQL Server 2008 Other environments, only as a reference

SQL SERVER 2008 failed to log on (SQL and Windows do not have corresponding permissions)