SQL Server 跨库/服务器查询

1 SQL Server 跨库/服务器查询

1.1 跨库查询

在同一服务器下的跨库查询较为简单，示例

Database.DatabaseSchema.DatabaseObject
# 示例 [SqlMatc] 数据库中，查询数据库[SQLATM]里的 DeptSales_copy 表里的所有数据
select * from [SQLATM].[dbo].[DeptSales_copy]
1
2
3

1.2 跨服务器查询

进行跨服务器查询前提是在本地服务器上创建链接服务器。

1.2.1 创建链接服务器

方法一：利用系统存储过程 sp_addlinkedserver

sp_addlinkedserver

创建链接服务器。 链接服务器提供对 OLE DB 数据源的分布式异类查询的访问权限。 使用 sp_addlinkedserver创建链接服务器后，可对此服务器运行分布式查询。 如果将链接服务器定义为 SQL Server 实例，则可以执行远程存储过程。

权限

语句 sp_addlinkedserver 需要 ALTER ANY LINKED SERVER 权限。 (“SQL Server Management Studio新建链接服务器”对话框的实现方式需要固定服务器角色的成员sysadmin身份。)

参数解读

sp_addlinkedserver [ @server = ] 'server' 	-- 链接服务器的名称
    [ , [ @srvproduct = ] 'product_name' ]  -- 链接服务器的 OLE DB 数据源的产品名
    										-- product_name值为 nvarchar (128) ，
    										-- 默认值为 NULL。 
    									 -- 如果值为SQL Server，则无需指provider_name、
    									 -- data_source、位置、provider_string和目录。
    										
    [ , [ @provider = ] 'provider_name' ] 	-- 唯一编程标识符。建议使用 MSOLEDBSQL 而不是 SQLNCLI。
    [ , [ @datasrc = ] 'data_source' ] -- 目的服务器地址
    [ , [ @location = ] 'location' ]   -- 本地登录
    [ , [ @provstr = ] 'provider_string' ] -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
    [ , [ @catalog = ] 'catalog' ] -- 与 OLE DB 提供程序建立连接时要使用的目录。
    
1
2
3
4
5
6
7
8
9
10
11
12
13

链接示例：

if exists(select * from sys.servers where name='LinkedServerName')
begin
	--删除运行本地与远程之间的用户映射
	execute sys.sp_droplinkedsrvlogin @rmtsrvname='LinkedServerName', @locallogin=null
	--删除链接服务器
	execute sys.sp_dropserver @server='LinkedServerName', @droplogins='droplogins'
end
go

EXEC sp_addlinkedserver
     @server = 'LinkedServerName'	-- 目的服务器别名
    ,@srvproduct = ''
    ,@provider = 'MSOLEDBSQL' 
    ,@datasrc = '192.168.3.21' -- 目的服务器IP地址
    ,@location = ''	-- 本地登录
    ,@provstr = '' -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
    --@catalog = '';	-- 指定目录 默认值为 NULL

-- 将凭据和选项添加到此链接服务器。
EXEC sp_addlinkedsrvlogin
	 @rmtsrvname = 'LinkedServerName'
	,@useself = 'false'	-- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器
	,@rmtuser = 'sa'	-- 登录名
	,@rmtpassword = 'root';	-- 密码

EXEC sp_serveroption 'LinkedServerName', 'rpc', true;	-- 从指定的服务器启用远程过程调用 (RPC)
EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;	-- 对指定的服务器启用 RPC。

-- 查询示例 select * from Server.Database.DatabaseSchema.DatabaseObject
SELECT name FROM [LinkedServerName].master.sys.databases;  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

此处将链接SQL Server服务器封装成了名为RemoteConnectionServer的存储过程方便使用

在此存储过程中需要提供四个参数，目的服务器别名（见名知义）、目的服务器IP、目的服务器登录名以及密码。即可创建一个服务器链接。（注意此存储过程只创建了一个简单的链接服务器，设置了必要的链接参数，使用时请确保参数够用。）

create proc RemoteConnectionServer
	@LinkedServerName nvarchar(255)	-- 目的服务器别名
	,@LinkedServerIP nvarchar(255) -- 目的服务器IP地址
	,@userName nvarchar(255)	-- 登录名
	,@password nvarchar(255)	-- 密码
as
	-- 查找链接服务器是否已创建，若创建则删除
	if exists(select * from sys.servers where name= @LinkedServerName)
	begin
		-- 删除运行本地与远程之间的用户映射
		execute sys.sp_droplinkedsrvlogin @rmtsrvname= @LinkedServerName, @locallogin=null
		--删除链接服务器
		execute sys.sp_dropserver @server=@LinkedServerName, @droplogins='droplogins'
	end

	-- 创建链接服务器 
	exec sp_addlinkedserver
		 @server = @LinkedServerName	-- 目的服务器别名
		,@srvproduct = ''
		,@provider = 'MSOLEDBSQL' 
		,@datasrc = @LinkedServerIP -- 目的服务器IP地址
		,@location = ''	-- 本地登录
		,@provstr = '' -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
		--@catalog = '';	-- 指定目录 默认值为 NULL

	-- 将凭据和选项添加到此链接服务器。
	exec sp_addlinkedsrvlogin
		 @rmtsrvname = @LinkedServerName
		,@useself = 'false'	-- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器
		,@rmtuser = @userName	-- 登录名
		,@rmtpassword = @password;	-- 密码

	-- 设置服务器选项
	exec sp_serveroption @LinkedServerName, 'rpc', true;	-- 从指定的服务器启用远程过程调用 (RPC)
	exec sp_serveroption @LinkedServerName, 'rpc out', true;	-- 对指定的服务器启用 RPC。

go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

执行存储过程

declare	@LinkedServerName nvarchar(255)	= 'linkName'
declare	@LinkedServerIP nvarchar(255) = '192.168.3.21'
declare	@userName nvarchar(255)	= 'sa'
declare	@password nvarchar(255)	= '****'
-- 执行
exec RemoteConnectionServer @LinkedServerName, @LinkedServerIP, @userName, @password

-- 测试
SELECT name FROM linkName.master.sys.databases;  
1
2
3
4
5
6
7
8
9

方法二：利用SSMS创建链接服务器

下面以链接192.168.2.21 SQL Server 服务器为例：

1 打开SSMS链接到本地服务器

2 选择服务器对象–>链接服务器

3 链接服务器 右键 新建链接服务器 常规页

PS：如果勾选服务器类型为"SQL Server"，则此处’‘链接服务器’'名必须为IP 地址。若选择"其他数据源"，此处的链接服务器名作为映射存在（别名）, 在数据源处填写IP地址。

当然你也可以选择其他数据源去链接 SQL Server 服务器。

4 选择“安全性”页

5 选择 “服务器选项” 将 RPC 与 RPC Out 设置为True，默认为False。

• RPC
  从指定的服务器启用远程过程调用 (RPC)。
• RPC Out
  对指定的服务器启用 RPC。
  

6 点击 “确定”，此时链接服务器目录下会出现你创建的连接服务器。

7 右键 测试链接

successs！

参考链接01：sp_addlinkedserver (Transact-SQL) - SQL Server | Microsoft Learn

参考链接02：创建链接服务器 - SQL Server | Microsoft Learn

1.2.2 跨库查询

-- 查询示例 select * from Server.Database.DatabaseSchema.DatabaseObject
SELECT * FROM [192.168.3.21].[GZ].[dbo].[Dept]
1
2

1.3 拓展：SQL Server 中所有权和用户与架构的分离

SQL Server 安全性的核心概念是对象的所有者具有管理这些对象的不可撤消的权限。 你不能删除对象所有者的特权，并且如果用户在数据库中拥有对象，你也不能将用户从此数据库中删除。

用户架构分离

通过用户架构分离，可实现管理数据库对象权限的更大灵活性。 架构是一个适用于数据库对象的命名容器，它使你能够将对象分组到单独的命名空间中。

用于引用对象的由四部分组成的命名语法指定架构名称。

Server.Database.DatabaseSchema.DatabaseObject
1

架构所有者和权限

任何数据库主体都可以拥有架构，并且一个主体可拥有多个架构。 您可以对架构应用安全规则，安全规则将由架构中的所有对象继承。 如果设置了对架构的访问权限，则当新对象添加到架构时，新对象会自动应用这些权限。 可以为用户分配一个默认的架构，且多个数据库用户可以共享同一架构。

默认情况下，当开发人员在架构中创建对象时，该对象由拥有架构的安全主体而不是开发人员拥有。 可以使用 ALTER AUTHORIZATION Transact-SQL 语句转移对象所有权。 尽管架构还可以包含由不同用户拥有的对象并且这些对象具有比分配给架构的权限更加细化的权限，但因为架构会增大管理权限的复杂度，因此不建议使用。 对象可以在架构之间移动，架构所有权也可以在主体之间转移。 可以在不影响架构的情况下删除数据库用户。

实现后向兼容性的内置架构

SQL Server 随附 9 个预定义架构，这些架构的名称与内置数据库用户和角色的名称相同：db_accessadmin、db_backupoperator、db_datareader、db_datawriter、db_ddladmin、db_denydatareader、db_denydatawriter、db_owner 和 db_securityadmin。 这些架构用于实现后向兼容性。 建议不要将它们用于用户对象。 可以删除与固定数据库角色同名的架构 - 除非它们已被使用，在这种情况下，drop-command 仅返回错误并阻止删除已使用的架构。 例如：

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin')
DROP SCHEMA [db_accessadmin]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_backupoperator')
DROP SCHEMA [db_backupoperator]
1
2
3
4
5
6

如果从模型数据库中删除这些架构，它们将不会显示在新数据库中。 不能删除包含对象的架构。

无法删除以下架构：

• dbo
• guest
• sys
• INFORMATION_SCHEMA

sys 和 INFORMATION_SCHEMA 架构是为系统对象而保留的。 您不能在这些架构中创建对象，而且不能删除它们。

dbo 架构

dbo 架构是每个数据库的默认架构。 默认情况下，使用 CREATE USER Transact-SQL 命令创建的用户的默认架构为 dbo。 dbo 架构由 dbo 用户帐户拥有。

默认架构被分配为 dbo 的用户不会继承 dbo 用户帐户的权限。 用户不从架构继承权限；架构权限由架构中包含的数据库对象继承。 用户的默认架构仅用于对象引用，以防用户在查询对象时省略架构。

当使用部分名称来引用数据库对象时，SQL Server 首先在用户的默认架构中查找。 如果在此处未找到该对象，则 SQL Server 其次将在 dbo 架构中查找。 如果对象不在 dbo 架构中，则会返回一个错误。

参考链接：SQL Server 中所有权和用户与架构的分离 - SQL Server | Microsoft Learn