Chapter 2 User authentication, Authorization, and Security (4): Restricting administrative permissions on the SA account

原文出处：http://blog.csdn.net/dba_huangzj/article/details/38817915，专题目录：http://blog.csdn.net/dba_huangzj/article/details/37906349

No person shall, without the consent of the author, publish in the form of "original" or be used for commercial purposes without any liability.

Last article: http://blog.csdn.net/dba_huangzj/article/details/38756693

Preface:

The SA account is a system administrator for SQL Server that can be enabled during installation and cannot be modified before SQL Server 2005, but can be renamed or disabled to reduce the risk of attack, starting at 2005. In subsequent versions, the SA is only for backward compatibility. Since SA is a well-known account, it is not recommended.

implementation:

1. Disable SA:

ALTER LOGIN [SA] DISABLE;

2. Rename SA:

ALTER LOGIN [SA] with name=[for not easily guessed name];

3. After renaming the SA, you can use the following query to find out what account the original SA is now:

SELECT * from sys.sql_logins WHERE principal_id = 1;

Where principal_id as 1,sid is 0x01 is the original SA.

principle:

When you install, the SA is disabled by default if you do not select mixed authentication. If you have a sysadmin or securityadmin fixed server role, you can modify the sa password. When you decide to rename the SA, check to see if any other applications are using this account, and renaming may cause these applications to fail.

How to manage when you do not have administrator privileges

I have seen many scenarios, because intentionally or unintentionally, all have administrator permissions of Windows, SQL account deleted, and SA is disabled or do not know the password, many operations can not be done, the solution to this problem is to rebuild the master library, see: http:// Msdn.microsoft.com/zh-cn/library/dd207003.aspx (Rebuild the system database). However, this will lose all login information unless you have the most recent master library backup. And you need to stop the SQL Server service.

There is another way, I have practiced:

Enable local accounts: 
1. First look at whether the account of this computer has administrator privileges, if not added on. 
2. Enter CMD in the Start menu search box, right-click to select to run 
as Administrator 3. At the command prompt, enter NET stop Mssqlservre stop MSSQLServer run (you can use this method if you have stopped) 
4. If 3 has a problem, prompts an error, you can start-->sql SERVER--> Configuration Tool--& Gt SQL Server service--> open SQL Server Properties--> advanced--> startup Parameters plus-M
5. If none of the above is true, switch to the installation path, that is, the Sqlservr.exe path under Binn 
: CD C : \program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\binn 
6. Execute the Sqlservr.exe, that is, Single-user mode entered 
7. Then login to the admin account to open a window and enter Sqlcmd-a 
8. Enter the action command you want to change. Here I need to add the native account 
  such as: Use 
master go 
CREATE LOGIN [need to add account number, General format is machine name \ login account name] from WINDOWS with Default_ Database=[master] 
go 
EXEC sp_addsrvrolemember @loginame =n ' machine name \ login account name ', @rolename =n ' sysadmin ' 
go 
to avoid errors, you can add a SQL account for a rainy month, or you can enable
the SA command 9. Restart SQL Server service login after the above operation is completed

Also take a look at the following blog:

Http://www.cnblogs.com/lyhabc/p/3513560.html

http://sqlserver-help.com/2012/02/08/ help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/

Next: http://blog.csdn.net/dba_huangzj/article/details/38844999