SA用户(“系统管理员”的简称)是MS SQL服务器的管理者账户。如果忘记服务器管理员账户的密码该怎么办呢?难道要重新安装SQL服务器或放弃所有数据内容吗?不管出于何种原因,如果你忘记了服务器的管理账户密码,并且被告知你无权进行更改,这篇文章将教给你几种重置SQL服务器SA密码的方法。
这些步骤适用于2005、2008、2012及更新的SQL服务器。
步骤
方法 1 的 4:
方法1:从命令提示符中更改SA密码
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/1\/16\/Reset-SA-Password-in-Sql-Server-Step-1-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-1-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/1\/16\/Reset-SA-Password-in-Sql-Server-Step-1-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-1-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
1
打开命令提示符,前往SQL服务器,输入命令osql –L。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/2\/29\/Reset-SA-Password-in-Sql-Server-Step-2-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-2-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/2\/29\/Reset-SA-Password-in-Sql-Server-Step-2-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-2-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
2
复制SQL服务器全名,输入:OSQL -S <这里输入服务器的名称> -E。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/5\/53\/Reset-SA-Password-in-Sql-Server-Step-3-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-3-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/5\/53\/Reset-SA-Password-in-Sql-Server-Step-3-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-3-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
3执行以下命令:sp_password NULL, ‘<这里插入新密码>’, ’sa’
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/4\/41\/Reset-SA-Password-in-Sql-Server-Step-4-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-4-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/4\/41\/Reset-SA-Password-in-Sql-Server-Step-4-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-4-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
4
完成重置。
方法 2 的 4:
方法2:使用SSMS重置SA密码
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/8\/8b\/Reset-SA-Password-in-Sql-Server-Step-5-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-5-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/8\/8b\/Reset-SA-Password-in-Sql-Server-Step-5-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-5-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
1
打开SSMS管理器。它通常位于开始菜单 -> 所有程序 -> “Microsoft SQL Server 2005” -> 找到“SQL Server Management Studio”程序。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/9\/9c\/Reset-SA-Password-in-Sql-Server-Step-6-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-6-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/9\/9c\/Reset-SA-Password-in-Sql-Server-Step-6-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-6-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
2
用windows身份验证连接SQL服务器。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/1\/1d\/Reset-SA-Password-in-Sql-Server-Step-7-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-7-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/1\/1d\/Reset-SA-Password-in-Sql-Server-Step-7-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-7-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
3
打开后,在左侧的对象资源管理器中展开“安全性”,在登录名下找到“sa”用户。右键点击它,选择属性。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/f\/f5\/Reset-SA-Password-in-Sql-Server-Step-8-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-8-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/f\/f5\/Reset-SA-Password-in-Sql-Server-Step-8-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-8-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
4
在“密码”和“确认密码”栏前方输入新密码,点击确定。
方法 3 的 4:
方法3:使用SQL密码恢复工具重置SA密码
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/2\/22\/Reset-SA-Password-in-Sql-Server-Step-9-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-9-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/2\/22\/Reset-SA-Password-in-Sql-Server-Step-9-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-9-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
1
运行SQL密码恢复程序,然后点击“打开”来导入master.mdf文件。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/e\/e4\/Reset-SA-Password-in-Sql-Server-Step-10-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-10-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/e\/e4\/Reset-SA-Password-in-Sql-Server-Step-10-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-10-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
2
选择SA用户名来重置忘记的SA密码,或选择其它忘记密码的用户名。点击“更改密码”来重置密码。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/c\/cb\/Reset-SA-Password-in-Sql-Server-Step-11-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-11-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/c\/cb\/Reset-SA-Password-in-Sql-Server-Step-11-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-11-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
3
输入新密码,点击“确定”。
方法 4 的 4:
方法4:当SA已被禁用时
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/b\/be\/Reset-SA-Password-in-Sql-Server-Step-12-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-12-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/b\/be\/Reset-SA-Password-in-Sql-Server-Step-12-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-12-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
1
这是一种额外的复杂方案。有时候你会遇到SA被禁用的情况。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/0\/05\/Reset-SA-Password-in-Sql-Server-Step-13-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-13-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/0\/05\/Reset-SA-Password-in-Sql-Server-Step-13-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-13-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
2
使用命令行的-m标签命令,以单个用户身份打开SQL服务器。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/e\/ec\/Reset-SA-Password-in-Sql-Server-Step-14-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-14-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/e\/ec\/Reset-SA-Password-in-Sql-Server-Step-14-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-14-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
3
以管理员身份运行。
-
{"smallUrl":"https:\/\/www.zenmeban.com\/images_en\/thumb\/5\/55\/Reset-SA-Password-in-Sql-Server-Step-15-Version-2.jpg\/v4-460px-Reset-SA-Password-in-Sql-Server-Step-15-Version-2.jpg","bigUrl":"https:\/\/www.zenmeban.com\/images\/thumb\/5\/55\/Reset-SA-Password-in-Sql-Server-Step-15-Version-2.jpg\/v4-728px-Reset-SA-Password-in-Sql-Server-Step-15-Version-2.jpg","smallWidth":460,"smallHeight":345,"bigWidth":728,"bigHeight":546,"licensing":"<div class=\"mw-parser-output\"><\/div>"}
4
在服务器身份页面,选择系统管理员,然后重置密码。
小提示
-
重置SA密码最简单有效的方法是使用SQL密码恢复工具。你可以使用几种SQL密码恢复工具来成功重置SQL的SA密码。如,“SQL Password Geeker”程序、“SQL Password Rescuer”程序。