查看或更改服务器属性 (SQL Server)View or Change Server Properties (SQL Server)

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本主题说明如何使用 SQL ServerSQL ServerSQL Server Management StudioSQL Server Management Studio或 SQL Server 配置管理器查看或更改 Transact-SQLTransact-SQL实例的属性。This topic describes how to view or change the properties of an instance of SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or SQL Server Configuration Manager.

本主题内容In This Topic

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 使用 sp_configure 时,必须在设置配置选项之后运行 RECONFIGURE 或 RECONFIGURE WITH OVERRIDE。When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. RECONFIGURE WITH OVERRIDE 语句通常专门用来设置那些使用起来应当十分小心的配置选项。The RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that should be used with extreme caution. 但是,RECONFIGURE WITH OVERRIDE 可用于所有的配置选项,并且可以用它代替 RECONFIGURE。However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.

    备注

    RECONFIGURE 在事务内部执行。RECONFIGURE executes within a transaction. 如果任意重新配置选项失败,则所有重新配置操作都将失效。If any of the reconfigure operations fail, none of the reconfigure operations will take effect.

  • 有些属性页会显示通过 Windows Management Instrumentation (WMI) 获得的信息。Some property pages present information obtained via Windows Management Instrumentation (WMI). 若要显示这些页,WMI 必须安装在运行 SQL Server Management StudioSQL Server Management Studio的计算机上。To display those pages, WMI must be installed on the computer running SQL Server Management StudioSQL Server Management Studio.

SecuritySecurity

权限Permissions

有关详细信息,请参阅 服务器级别角色For more information, see Server-Level Roles.

默认情况下,所有用户都具备不带参数或仅带第一个参数的 sp_configure 的执行权限。Execute permissions on sp_configure with no parameters or with only the first parameter are granted to all users by default. 若要执行带两个参数的 sp_configure 以更改配置选项或运行 RECONFIGURE 语句,则用户必须具备 ALTER SETTINGS 服务器级别的权限。To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

使用 SQL Server Management StudioUsing SQL Server Management Studio

查看或更改服务器属性To view or change server properties

  1. 在“对象资源管理器”中,右键单击服务器,再单击“属性” 。In Object Explorer, right-click a server, and then click Properties.

  2. “服务器属性” 对话框中,单击某页以查看或更改有关该页的服务器信息。In the Server Properties dialog box, click a page to view or change server information about that page. 某些属性是只读属性。Some properties are read-only.

使用 Transact-SQLUsing Transact-SQL

通过使用 SERVERPROPERTY 内置函数查看服务器属性To view server properties by using the SERVERPROPERTY built-in function

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例在 语句中使用 SERVERPROPERTY SELECT 内置函数,以返回有关当前服务器的信息。This example uses the SERVERPROPERTY built-in function in a SELECT statement to return information about the current server. 如果基于 Windows 的服务器上安装了多个 SQL ServerSQL Server 实例,而且客户端必须打开另一个到当前连接所使用的同一实例连接,则此方案很有用。This scenario is useful when there are multiple instances of SQL ServerSQL Server installed on a Windows-based server, and the client must open another connection to the same instance that is used by the current connection.

    SELECT CONVERT( sysname, SERVERPROPERTY('servername'));  
    GO  
    

通过使用 sys.servers 目录视图查看服务器属性To view server properties by using the sys.servers catalog view

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例查询 sys.servers 目录视图,以返回当前服务器的名称 (name) 和 ID (server_id),以及用于连接到链接服务器的 OLE DB 访问接口 (provider) 的名称。This example queries the sys.servers catalog view to return the name (name) and ID (server_id) of the current server, and the name of the OLE DB provider (provider) for connecting to a linked server.

    USE AdventureWorks2012;   
    GO  
    SELECT name, server_id, provider  
    FROM sys.servers ;   
    GO  
    
    

通过使用 sys.configurations 目录视图查看服务器属性To view server properties by using the sys.configurations catalog view

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例查询 sys.configurations 目录视图,以返回有关当前服务器上的各个服务器配置选项的信息。This example queries the sys.configurations catalog view to return information about each server configuration option on the current server. 该示例返回选项的名称 (name) 和说明 (description),以及该选项是否为高级选项 (is_advanced)。The example returns the name (name) and description (description) of the option and whether the option is an advanced option (is_advanced).

    USE AdventureWorks2012;   
    GO  
    SELECT name, description, is_advanced  
    FROM sys.configurations ;   
    GO  
    
    

通过使用 sp_configure 更改服务器属性To change a server property by using sp_configure

  1. 连接到 数据库引擎Database EngineConnect to the 数据库引擎Database Engine.

  2. 在标准菜单栏上,单击 “新建查询”From the Standard bar, click New Query.

  3. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute. 此示例显示如何使用 sp_configure 更改服务器属性。This example shows how to use sp_configure to change a server property. 本示例将 fill factor 选项的值更改为 100The example changes the value of the fill factor option to 100. 必须重新启动服务器,更改才会生效。The server must be restarted before the change can take effect.

Use AdventureWorks2012;  
GO  
sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'fill factor', 100;  
GO  
RECONFIGURE;  
GO  

有关详细信息,请参阅 服务器配置选项 (SQL Server)的计算机上。For more information, see Server Configuration Options (SQL Server).

使用 SQL Server 配置管理器Using SQL Server Configuration Manager

可以通过使用 SQL Server 配置管理器查看或更改某些服务器属性。Some server properties can be viewed or changed by using SQL Server Configuration Manager. 例如,您可以查看 SQL Server 实例的版本,或更改存储错误日志文件的位置。For example, you can view the version and edition of the instance of SQL Server, or change the location where error log files are stored. 也可以通过查询 服务器相关的动态管理视图和函数来查看这些属性。These properties can also be viewed by querying the Server-Related Dynamic Management Views and Functions.

查看或更改服务器属性To view or change server properties

  1. “开始” 菜单中,依次指向 “所有程序”Microsoft SQL ServerMicrosoft SQL Server“配置工具”,然后单击 “SQL Server 配置管理器”On the Start menu, point to All Programs, point to Microsoft SQL ServerMicrosoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.

  2. “SQL Server 配置管理器” 中,单击 “SQL Server 服务”In SQL Server Configuration Manager, click SQL Server Services.

  3. 在详细信息窗格中,右键单击“SQL Server (<instancename>)”,然后单击“属性”。In the details pane, right-click SQL Server (<instancename>), and then click Properties.

  4. 在“SQL Server (<instancename>)”的“属性”对话框中,更改“服务”选项卡或“高级”选项卡上的服务器属性,然后单击“确定”。In the SQL Server (<instancename>) Properties dialog box, change the server properties on the Service tab or the Advanced tab, and then click OK.

跟进:更改服务器属性之后Follow Up: After you change server properties

对于某些属性,可能必须重新启动服务器,才能使更改生效。For some properties, the server might have to be restarted before the change can take effect.

另请参阅See Also

服务器配置选项 (SQL Server) Server Configuration Options (SQL Server)
SET 语句 (Transact-SQL) SET Statements (Transact-SQL)
SERVERPROPERTY (Transact-SQL) SERVERPROPERTY (Transact-SQL)
sp_configure (Transact-SQL) sp_configure (Transact-SQL)
RECONFIGURE (Transact-SQL) RECONFIGURE (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
在 SQL Server 工具中将 WMI 配置为显示服务器状态 Configure WMI to Show Server Status in SQL Server Tools
SQL Server 配置管理器 SQL Server Configuration Manager
配置函数 (Transact-SQL) Configuration Functions (Transact-SQL)
与服务器相关的动态管理视图和函数 (Transact-SQL)Server-Related Dynamic Management Views and Functions (Transact-SQL)