Chapter 2 user authentication, authorization, and security (4): restrict the management permissions of the SA account

Source: Workshop

Without the consent of the author, no one shall be published in the form of "original" or used for commercial purposes. I am not responsible for any legal liability.

Previous Article: http://blog.csdn.net/dba_huangzj/article/details/38756693

 

Preface:

 

The SA account is the system administrator of SQL server. It can be enabled during installation. Before SQL Server 2005, this account cannot be modified, but it starts from 2005, you can rename or disable SA to reduce the risk of being attacked. In later versions, SA is only backward compatible. Sa is a well-known account and is not recommended.

 

Implementation:

 

1. Disable SA:

ALTER LOGIN [sa] DISABLE;

2. Rename SA:

Alter login [SA] with name = [replace with a name that is not easily guessed];

3. After renaming the SA, you can use the following query to find the current account of the original SA:

 

SELECT * FROM sys.sql_logins WHERE principal_id = 1;

The principal_id is 1, and the SID is 0x01, which is the original SA.

 

Principle:

 

Sa is disabled by default if mixed authentication is not selected during installation. If you have a fixed server role of SysAdmin or securityadmin, you can change the SA password. When you decide to rename the SA, check whether other applications are using this account. renaming may cause these applications to fail.

 

How to manage an account without administrator privilege

 

I have seen many scenarios. Because I intentionally or unintentionally deleted all windows and SQL accounts with administrator permissions, and SA was disabled or did not know the password, many operations were not allowed, the solution to this problem can be to rebuild the master database, as shown in: http://msdn.microsoft.com/zh-cn/library/dd207003.aspx (rebuild the system database ). However, all logon information will be lost unless you have backed up the latest master database. And you need to stop the SQL Server service.

There is another method that I have practiced:

Enable local account: 1. Check whether the local account has administrator permissions. 2. enter cmd in the search box of the Start menu, right-click it, and choose "Run as administrator. enter "net stop mssqlservre" in the command prompt to stop MSSQLServer (if it has been stopped, you can skip this method) 4. if 3 is faulty, an error is reported, you can choose "start"> "SQL Server"> "configuration tool"> "SQL Server Service"> "enable" SQL Server properties ">" advanced ">" startup parameters ", and add" -m5." to the installation parameters. The path of sqlservr.exe under "binn" is CD C: \ Program Files \ Microsoft SQL Server \ mssql10.mssqlserver \ MSSQL \ binn 6. execute sqlservr.exe, that is, the single user mode enters 7. then, log on to the administrator account to open a window and enter sqlcmd-A 8. enter the operation command you want to change. Here, I need to add the local account, for example, use master go create login [account to be added, the 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 in order to avoid errors, you can add an SQL account for your temporary needs, or enable the SA command 9. after completing the preceding operations, restart the SQL Server service to log on.

 

You can also read 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/

Chapter 2 user authentication, authorization, and security (4): restrict the management permissions of the SA account