Windows 7 中管理员用户登录 SQL Server 2008 数据库引擎失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2833373/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server 2008 database engine login failed for administrator user in windows 7
提问by Sadegh
I installed SQL Server 2008 Enterprise Edition on Windows 7 Ultimate from sadegh user.
我从sadegh 用户那里在Windows 7 Ultimate 上安装了SQL Server 2008 Enterprise Edition。
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:
此帐户以管理员角色存在。几天后，我从 Windows 中删除了 sadegh 用户，现在我使用的是管理员用户。但是我无法使用 Windows 身份验证方法登录到 SQL Server 数据库引擎，并且收到此错误消息：
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
标题：连接到服务器
无法连接到 SADEGH-PC。
附加信息：
用户“Sadegh-PC\Administrator”登录失败。（Microsoft SQL Server，错误：18456）
如需帮助，请单击：http: //go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
please help me! thanks
请帮我！谢谢
回答by Garett
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.
根据安装 SQL Server 的模式（Windows 身份验证或混合模式），您可能需要做一些事情。如果您以混合模式安装，您可以作为 sa 登录并在安全部分下添加管理员用户作为登录。确保将 sysadmin 角色分配给用户，该角色可在服务器角色下找到。
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.
如果在安装过程中选择了 Windows 身份验证模式，那么您可以做一些事情，但我认为最简单的方法是重新创建用户 sadegh，以该用户身份登录 SQL Server，然后按照上一步添加管理员用户作为登录。
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:
更新：如果所有其他方法都失败了，您可以在单用户模式下运行 SQL Server，这允许与服务器的单一连接，并启用/更改 sa 密码。为此，您可以：
- Open the command prompt (Right-Click on and select "Run As Administrator")
- From the command prompt type net stop MSSQLSERVER
- Next type net start MSSQLSERVER /m
- Open SQL Server Management Studio. Do not login, cancel the login dialog.
- From the file Menu select New->Database engine query, and login (Make sure you use the host name and not localhost).
- Execute the query ALTER LOGIN sa WITH PASSWORD = '';to reset the password (if the sa is not enabled then type ALTER LOGIN sa ENABLEto do so)
- Login with the sa user and add the Administrator user.
- 打开命令提示符（右键单击并选择“以管理员身份运行”）
- 从命令提示符键入net stop MSSQLSERVER
- 接下来键入net start MSSQLSERVER /m
- 打开 SQL Server 管理工作室。不要登录，取消登录对话框。
- 从文件菜单中选择 New->Database engine query，然后登录（确保使用主机名而不是 localhost）。
- 执行查询ALTER LOGIN sa WITH PASSWORD = ''; 重置密码（如果未启用 sa 则键入ALTER LOGIN sa ENABLE这样做）
- 使用 sa 用户登录并添加管理员用户。
EDIT:
编辑：
As indicated by @jimasp in the comments, for step 6 you may have to do ALTER LOGIN sa WITH PASSWORD = '' UNLOCK;, because the sa account may be locked from too many login attempts.
正如@jimasp 在评论中所指出的，对于第 6 步，您可能必须执行ALTER LOGIN sa WITH PASSWORD = '' UNLOCK; ，因为 sa 帐户可能会因登录尝试次数过多而被锁定。
Tested on Windows 7 Ultimate with SQL Server 2008 Standard. Hope this helps.
在 Windows 7 Ultimate 和 SQL Server 2008 Standard 上测试。希望这可以帮助。
回答by Kenneth
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.
在以前版本的 SQL 中，BUILTIN\Administrators 组被赋予了 sysadmin 角色。在 SQL 2008 中，默认情况下，本地管理员没有登录权限。只有执行安装的用户才具有默认的 sa 权限。在安装过程中，您可以选择添加其他用户，但您显然没有。
You will need to login as sa and configure whatever windows logins are required (such as the local administrator account).
您需要以 sa 身份登录并配置所需的任何 Windows 登录名（例如本地管理员帐户）。
回答by Jeremy W
If don't want to use SQL Server Management Studio and you can login as the user who installed SQL server, try this from a command prompt:
如果不想使用 SQL Server Management Studio 并且您可以以安装 SQL Server 的用户身份登录，请从命令提示符尝试此操作：
OSQL -S <insert_servername_here> -E
1> EXEC sp_password NULL, ‘<insert_new_password_here>', ‘sa'
2> GO
回答by Ally
The reason I was getting this error was due to the same connection being opened multiple times but it never being closed and such the connection pool was maxed out.
我收到此错误的原因是同一个连接被多次打开，但从未关闭，因此连接池已用尽。
In short, check your closing all your connections.
简而言之，检查您关闭所有连接。
