How to create an SA privilege account in SQL Server?

To create a System Administrator (SA) privilege account in SQL Server, follow these steps:

1. Open SQL Server Management Studio (SSMS): Connect to your SQL Server instance using an existing account with administrative privileges.

2. Navigate to Security: In the Object Explorer, expand the server node, then expand the "Security" folder.

3. Create a New Login:

   • Right-click on "Logins" and select "New Login...".
   • In the "Login - New" window, enter a login name (e.g., NewAdmin).
   • Select "SQL Server authentication" and set a strong password.
   • Uncheck "Enforce password policy" if needed (not recommended for production).

4. Grant SA Privileges:

   • Go to the "Server Roles" page in the same window.
   • Check the sysadmin role. This grants full administrative privileges, equivalent to the SA account.
   • Click "OK" to create the login.

5. Verify the Account:

   • Disconnect and reconnect using the new login (NewAdmin).
   • Try executing administrative commands (e.g., CREATE DATABASE) to confirm the privileges.

Example:

-- After logging in as the new admin, verify permissions
SELECT IS_SRVROLEMEMBER('sysadmin'); -- Should return 1 (true)

Cloud Recommendation:
If you're using SQL Server on Tencent Cloud, consider leveraging TencentDB for SQL Server for managed database services. It simplifies administration, provides high availability, and supports automated backups. For custom setups, use Tencent Cloud Virtual Machine (CVM) to host SQL Server and follow the steps above.

Security Note: Avoid using the default sa account in production. Instead, create dedicated admin accounts with least-privilege principles.