3. SqlServer FAQs

Problem description :

SqlServer backup report has no permission.

Solution :

NT_AUTHORITY/SYSTEM users with public and sysadmin permissions.

-- 检查并创建登录用户
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'NT AUTHORITY\SYSTEM')
BEGIN
    CREATE LOGIN [NT AUTHORITY\SYSTEM] FROM WINDOWS;
END;

-- 检查是否已是 sysadmin
IF NOT EXISTS (
    SELECT 1 
    FROM sys.server_role_members srm
    JOIN sys.server_principals sp ON srm.member_principal_id = sp.principal_id
    JOIN sys.server_principals rl ON srm.role_principal_id = rl.principal_id
    WHERE sp.name = 'NT AUTHORITY\SYSTEM'
      AND rl.name = 'sysadmin'
)
BEGIN
    -- 分配 sysadmin 角色
    EXEC sp_addsrvrolemember 'NT AUTHORITY\SYSTEM', 'sysadmin';
END;

Problem description:

How to confirm Sqlserver instance Port Number?

Solution:

The default instance Port Number of SqlSever is 1433. If multiple SqlServers are installed on a client, the following methods can be used to confirm their respective instance Port Numbers:

1. SQL Server Configuration Manager :

   1. Open SQL Server Configuration Manager.

   2. Locate SQL Server Network Configuration > Confirm the protocol (such as TCP/IP) for the corresponding instance.

   3. Right-click on TCP/IP and select "Properties".

   4. In the "IP Address" tab, you can see the Port Number corresponding to each IP address.

2. Using SQL Server Management Studio (SSMS) :

   1. Connect to the SQL Server instance.

   2. Execute the following SQL query to obtain the Port Number of the instance:

   3. SELECT 
          SERVERPROPERTY('InstanceName') AS InstanceName, 
          SERVERPROPERTY('MachineName') AS MachineName, 
          SERVERPROPERTY('ProcessID') AS ProcessID, 
          local_net_address AS IPAddress, 
          local_tcp_port AS Port
      FROM sys.dm_exec_connections
      WHERE local_net_address IS NOT NULL;

3. View the SQL Server error log :

   1. SQL Server records port information in the error log when it starts. You can use the following query to obtain the path of the error log:

   2. EXEC xp_readerrorlog 0, 1, N'Server is listening on'

   3. Find the row containing "Server is listening on", which lists the Port Number of the instance.