SQL Server 跨库/服务器查询

1 SQL Server 跨库/服务器查询

1.1 跨库查询

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

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

复制代码

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身份。)
参数解读

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

复制代码

链接示例：

1. if exists(select * from sys.servers where name='LinkedServerName')
2. begin
3.         --删除运行本地与远程之间的用户映射
4.         execute sys.sp_droplinkedsrvlogin @rmtsrvname='LinkedServerName', @locallogin=null
5.         --删除链接服务器
6.         execute sys.sp_dropserver @server='LinkedServerName', @droplogins='droplogins'
7. end
8. go
10. EXEC sp_addlinkedserver
11.      @server = 'LinkedServerName'        -- 目的服务器别名
12.     ,@srvproduct = ''
13.     ,@provider = 'MSOLEDBSQL'
14.     ,@datasrc = '192.168.3.21' -- 目的服务器IP地址
15.     ,@location = ''        -- 本地登录
16.     ,@provstr = '' -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
17.     --@catalog = '';        -- 指定目录 默认值为 NULL
19. -- 将凭据和选项添加到此链接服务器。
20. EXEC sp_addlinkedsrvlogin
21.          @rmtsrvname = 'LinkedServerName'
22.         ,@useself = 'false'        -- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器
23.         ,@rmtuser = 'sa'        -- 登录名
24.         ,@rmtpassword = 'root';        -- 密码
26. EXEC sp_serveroption 'LinkedServerName', 'rpc', true;        -- 从指定的服务器启用远程过程调用 (RPC)
27. EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;        -- 对指定的服务器启用 RPC。
29. -- 查询示例 select * from Server.Database.DatabaseSchema.DatabaseObject
31. SELECT name FROM [LinkedServerName].master.sys.databases;  

复制代码

此处将链接SQL Server服务器封装成了名为RemoteConnectionServer的存储过程方便利用
在此存储过程中需要提供四个参数，目标服务器别名（见名知义）、目标服务器IP、目标服务器登录名以及暗码。即可创建一个服务器链接。（留意此存储过程只创建了一个简单的链接服务器，设置了必要的链接参数，利用时请确保参数够用。）

1. create proc RemoteConnectionServer
2.         @LinkedServerName nvarchar(255)        -- 目的服务器别名
3.         ,@LinkedServerIP nvarchar(255) -- 目的服务器IP地址
4.         ,@userName nvarchar(255)        -- 登录名
5.         ,@password nvarchar(255)        -- 密码
6. as
7.         -- 查找链接服务器是否已创建，若创建则删除
8.         if exists(select * from sys.servers where name= @LinkedServerName)
9.         begin
10.                 -- 删除运行本地与远程之间的用户映射
11.                 execute sys.sp_droplinkedsrvlogin @rmtsrvname= @LinkedServerName, @locallogin=null
12.                 --删除链接服务器
13.                 execute sys.sp_dropserver @server=@LinkedServerName, @droplogins='droplogins'
14.         end
16.         -- 创建链接服务器
17.         exec sp_addlinkedserver
18.                  @server = @LinkedServerName        -- 目的服务器别名
19.                 ,@srvproduct = ''
20.                 ,@provider = 'MSOLEDBSQL'
21.                 ,@datasrc = @LinkedServerIP -- 目的服务器IP地址
22.                 ,@location = ''        -- 本地登录
23.                 ,@provstr = '' -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。
24.                 --@catalog = '';        -- 指定目录 默认值为 NULL
26.         -- 将凭据和选项添加到此链接服务器。
27.         exec sp_addlinkedsrvlogin
28.                  @rmtsrvname = @LinkedServerName
29.                 ,@useself = 'false'        -- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器
30.                 ,@rmtuser = @userName        -- 登录名
31.                 ,@rmtpassword = @password;        -- 密码
33.         -- 设置服务器选项
34.         exec sp_serveroption @LinkedServerName, 'rpc', true;        -- 从指定的服务器启用远程过程调用 (RPC)
35.         exec sp_serveroption @LinkedServerName, 'rpc out', true;        -- 对指定的服务器启用 RPC。
37. go

复制代码

执行存储过程

1. declare        @LinkedServerName nvarchar(255)        = 'linkName'
2. declare        @LinkedServerIP nvarchar(255) = '192.168.3.21'
3. declare        @userName nvarchar(255)        = 'sa'
4. declare        @password nvarchar(255)        = '****'
5. -- 执行
6. exec RemoteConnectionServer @LinkedServerName, @LinkedServerIP, @userName, @password
8. -- 测试
9. SELECT name FROM linkName.master.sys.databases;  

复制代码

方法二：利用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 跨库查询

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

复制代码

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

SQL Server 安全性的焦点概念是对象的所有者具有管理这些对象的不可撤消的权限。 你不能删除对象所有者的特权，而且如果用户在数据库中拥有对象，你也不能将用户今后数据库中删除。
用户架构分离
通过用户架构分离，可实现管理数据库对象权限的更大机动性。 架构是一个适用于数据库对象的定名容器，它使你能够将对象分组到单独的定名空间中。
用于引用对象的由四部分组成的定名语法指定架构名称。

1. Server.Database.DatabaseSchema.DatabaseObject

复制代码

架构所有者和权限
任何数据库主体都可以拥有架构，而且一个主体可拥有多个架构。 您可以对架构应用安全规则，安全规则将由架构中的所有对象继承。 如果设置了对架构的访问权限，则当新对象添加到架构时，新对象会自动应用这些权限。 可以为用户分配一个默认的架构，且多个数据库用户可以共享同一架构。
默认情况下，当开发人员在架构中创建对象时，该对象由拥有架构的安全主体而不是开发人员拥有。 可以利用 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 仅返回错误并阻止删除已利用的架构。 比方：

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

复制代码

如果从模型数据库中删除这些架构，它们将不会表现在新数据库中。 不能删除包含对象的架构。
无法删除以下架构：

• 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

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。