1 SQL Server 跨庫(kù)/服務(wù)器查詢

1.1 跨庫(kù)查詢

在同一服務(wù)器下的跨庫(kù)查詢較為簡(jiǎn)單，示例

Database.DatabaseSchema.DatabaseObject
# 示例 [SqlMatc] 數(shù)據(jù)庫(kù)中，查詢數(shù)據(jù)庫(kù)[SQLATM]里的 DeptSales_copy 表里的所有數(shù)據(jù)
select * from [SQLATM].[dbo].[DeptSales_copy]

1.2 跨服務(wù)器查詢

進(jìn)行跨服務(wù)器查詢前提是在本地服務(wù)器上創(chuàng)建鏈接服務(wù)器。

1.2.1 創(chuàng)建鏈接服務(wù)器

方法一：利用系統(tǒng)存儲(chǔ)過程 sp_addlinkedserver

sp_addlinkedserver

創(chuàng)建鏈接服務(wù)器。 鏈接服務(wù)器提供對(duì) OLE DB 數(shù)據(jù)源的分布式異類查詢的訪問權(quán)限。 使用 sp_addlinkedserver創(chuàng)建鏈接服務(wù)器后，可對(duì)此服務(wù)器運(yùn)行分布式查詢。 如果將鏈接服務(wù)器定義為 SQL Server 實(shí)例，則可以執(zhí)行遠(yuǎn)程存儲(chǔ)過程。

權(quán)限

語(yǔ)句 sp_addlinkedserver 需要 ALTER ANY LINKED SERVER 權(quán)限。 (“SQL Server Management Studio新建鏈接服務(wù)器”對(duì)話框的實(shí)現(xiàn)方式需要固定服務(wù)器角色的成員sysadmin身份。)

參數(shù)解讀

sp_addlinkedserver [ @server = ] 'server' 	-- 鏈接服務(wù)器的名稱
    [ , [ @srvproduct = ] 'product_name' ]  -- 鏈接服務(wù)器的 OLE DB 數(shù)據(jù)源的產(chǎn)品名
    										-- product_name值為 nvarchar (128) ，
    										-- 默認(rèn)值為 NULL。 
    									 -- 如果值為SQL Server，則無(wú)需指provider_name、
    									 -- data_source、位置、provider_string和目錄。
    										
    [ , [ @provider = ] 'provider_name' ] 	-- 唯一編程標(biāo)識(shí)符。建議使用 MSOLEDBSQL 而不是 SQLNCLI。
    [ , [ @datasrc = ] 'data_source' ] -- 目的服務(wù)器地址
    [ , [ @location = ] 'location' ]   -- 本地登錄
    [ , [ @provstr = ] 'provider_string' ] -- 標(biāo)識(shí)唯一數(shù)據(jù)源的特定于 OLE DB 提供程序的連接字符串。
    [ , [ @catalog = ] 'catalog' ] -- 與 OLE DB 提供程序建立連接時(shí)要使用的目錄。
    

鏈接示例：

if exists(select * from sys.servers where name='LinkedServerName')
begin
	--刪除運(yùn)行本地與遠(yuǎn)程之間的用戶映射
	execute sys.sp_droplinkedsrvlogin @rmtsrvname='LinkedServerName', @locallogin=null
	--刪除鏈接服務(wù)器
	execute sys.sp_dropserver @server='LinkedServerName', @droplogins='droplogins'
end
go

EXEC sp_addlinkedserver
     @server = 'LinkedServerName'	-- 目的服務(wù)器別名
    ,@srvproduct = ''
    ,@provider = 'MSOLEDBSQL' 
    ,@datasrc = '192.168.3.21' -- 目的服務(wù)器IP地址
    ,@location = ''	-- 本地登錄
    ,@provstr = '' -- 標(biāo)識(shí)唯一數(shù)據(jù)源的特定于 OLE DB 提供程序的連接字符串。
    --@catalog = '';	-- 指定目錄 默認(rèn)值為 NULL

-- 將憑據(jù)和選項(xiàng)添加到此鏈接服務(wù)器。
EXEC sp_addlinkedsrvlogin
	 @rmtsrvname = 'LinkedServerName'
	,@useself = 'false'	-- 是否通過模擬登錄 or 顯示的提交登錄名和密碼鏈接到遠(yuǎn)程服務(wù)器
	,@rmtuser = 'sa'	-- 登錄名
	,@rmtpassword = 'root';	-- 密碼

EXEC sp_serveroption 'LinkedServerName', 'rpc', true;	-- 從指定的服務(wù)器啟用遠(yuǎn)程過程調(diào)用 (RPC)
EXEC sp_serveroption 'LinkedServerName', 'rpc out', true;	-- 對(duì)指定的服務(wù)器啟用 RPC。

-- 查詢示例 select * from Server.Database.DatabaseSchema.DatabaseObject
SELECT name FROM [LinkedServerName].master.sys.databases;  

此處將鏈接SQL Server服務(wù)器封裝成了名為RemoteConnectionServer的存儲(chǔ)過程方便使用

在此存儲(chǔ)過程中需要提供四個(gè)參數(shù)，目的服務(wù)器別名（見名知義）、目的服務(wù)器IP、目的服務(wù)器登錄名以及密碼。即可創(chuàng)建一個(gè)服務(wù)器鏈接。（注意此存儲(chǔ)過程只創(chuàng)建了一個(gè)簡(jiǎn)單的鏈接服務(wù)器，設(shè)置了必要的鏈接參數(shù)，使用時(shí)請(qǐng)確保參數(shù)夠用。）

create proc RemoteConnectionServer
	@LinkedServerName nvarchar(255)	-- 目的服務(wù)器別名
	,@LinkedServerIP nvarchar(255) -- 目的服務(wù)器IP地址
	,@userName nvarchar(255)	-- 登錄名
	,@password nvarchar(255)	-- 密碼
as
	-- 查找鏈接服務(wù)器是否已創(chuàng)建，若創(chuàng)建則刪除
	if exists(select * from sys.servers where name= @LinkedServerName)
	begin
		-- 刪除運(yùn)行本地與遠(yuǎn)程之間的用戶映射
		execute sys.sp_droplinkedsrvlogin @rmtsrvname= @LinkedServerName, @locallogin=null
		--刪除鏈接服務(wù)器
		execute sys.sp_dropserver @server=@LinkedServerName, @droplogins='droplogins'
	end

	-- 創(chuàng)建鏈接服務(wù)器 
	exec sp_addlinkedserver
		 @server = @LinkedServerName	-- 目的服務(wù)器別名
		,@srvproduct = ''
		,@provider = 'MSOLEDBSQL' 
		,@datasrc = @LinkedServerIP -- 目的服務(wù)器IP地址
		,@location = ''	-- 本地登錄
		,@provstr = '' -- 標(biāo)識(shí)唯一數(shù)據(jù)源的特定于 OLE DB 提供程序的連接字符串。
		--@catalog = '';	-- 指定目錄 默認(rèn)值為 NULL

	-- 將憑據(jù)和選項(xiàng)添加到此鏈接服務(wù)器。
	exec sp_addlinkedsrvlogin
		 @rmtsrvname = @LinkedServerName
		,@useself = 'false'	-- 是否通過模擬登錄 or 顯示的提交登錄名和密碼鏈接到遠(yuǎn)程服務(wù)器
		,@rmtuser = @userName	-- 登錄名
		,@rmtpassword = @password;	-- 密碼

	-- 設(shè)置服務(wù)器選項(xiàng)
	exec sp_serveroption @LinkedServerName, 'rpc', true;	-- 從指定的服務(wù)器啟用遠(yuǎn)程過程調(diào)用 (RPC)
	exec sp_serveroption @LinkedServerName, 'rpc out', true;	-- 對(duì)指定的服務(wù)器啟用 RPC。

go

執(zhí)行存儲(chǔ)過程

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

-- 測(cè)試
SELECT name FROM linkName.master.sys.databases;  

方法二：利用SSMS創(chuàng)建鏈接服務(wù)器

下面以鏈接192.168.2.21 SQL Server 服務(wù)器為例：

1 打開SSMS鏈接到本地服務(wù)器

2 選擇服務(wù)器對(duì)象–>鏈接服務(wù)器

3 鏈接服務(wù)器 右鍵 新建鏈接服務(wù)器 常規(guī)頁(yè)

PS：如果勾選服務(wù)器類型為"SQL Server"，則此處’‘鏈接服務(wù)器’'名必須為IP 地址。若選擇"其他數(shù)據(jù)源"，此處的鏈接服務(wù)器名作為映射存在（別名）, 在數(shù)據(jù)源處填寫IP地址。

當(dāng)然你也可以選擇其他數(shù)據(jù)源去鏈接 SQL Server 服務(wù)器。

4 選擇“安全性”頁(yè)

5 選擇 “服務(wù)器選項(xiàng)” 將 RPC 與 RPC Out 設(shè)置為True，默認(rèn)為False。

• RPC
  從指定的服務(wù)器啟用遠(yuǎn)程過程調(diào)用 (RPC)。
• RPC Out
  對(duì)指定的服務(wù)器啟用 RPC。

6 點(diǎn)擊 “確定”，此時(shí)鏈接服務(wù)器目錄下會(huì)出現(xiàn)你創(chuàng)建的連接服務(wù)器。

7 右鍵 測(cè)試鏈接

successs！

參考鏈接01：sp_addlinkedserver (Transact-SQL) - SQL Server | Microsoft Learn

參考鏈接02：創(chuàng)建鏈接服務(wù)器 - SQL Server | Microsoft Learn

1.2.2 跨庫(kù)查詢

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

1.3 拓展：SQL Server 中所有權(quán)和用戶與架構(gòu)的分離

SQL Server 安全性的核心概念是對(duì)象的所有者具有管理這些對(duì)象的不可撤消的權(quán)限。 你不能刪除對(duì)象所有者的特權(quán)，并且如果用戶在數(shù)據(jù)庫(kù)中擁有對(duì)象，你也不能將用戶從此數(shù)據(jù)庫(kù)中刪除。

用戶架構(gòu)分離

通過用戶架構(gòu)分離，可實(shí)現(xiàn)管理數(shù)據(jù)庫(kù)對(duì)象權(quán)限的更大靈活性。 架構(gòu)是一個(gè)適用于數(shù)據(jù)庫(kù)對(duì)象的命名容器，它使你能夠?qū)?duì)象分組到單獨(dú)的命名空間中。

用于引用對(duì)象的由四部分組成的命名語(yǔ)法指定架構(gòu)名稱。

Server.Database.DatabaseSchema.DatabaseObject

架構(gòu)所有者和權(quán)限

任何數(shù)據(jù)庫(kù)主體都可以擁有架構(gòu)，并且一個(gè)主體可擁有多個(gè)架構(gòu)。 您可以對(duì)架構(gòu)應(yīng)用安全規(guī)則，安全規(guī)則將由架構(gòu)中的所有對(duì)象繼承。 如果設(shè)置了對(duì)架構(gòu)的訪問權(quán)限，則當(dāng)新對(duì)象添加到架構(gòu)時(shí)，新對(duì)象會(huì)自動(dòng)應(yīng)用這些權(quán)限。 可以為用戶分配一個(gè)默認(rèn)的架構(gòu)，且多個(gè)數(shù)據(jù)庫(kù)用戶可以共享同一架構(gòu)。

默認(rèn)情況下，當(dāng)開發(fā)人員在架構(gòu)中創(chuàng)建對(duì)象時(shí)，該對(duì)象由擁有架構(gòu)的安全主體而不是開發(fā)人員擁有。 可以使用 ALTER AUTHORIZATION Transact-SQL 語(yǔ)句轉(zhuǎn)移對(duì)象所有權(quán)。 盡管架構(gòu)還可以包含由不同用戶擁有的對(duì)象并且這些對(duì)象具有比分配給架構(gòu)的權(quán)限更加細(xì)化的權(quán)限，但因?yàn)榧軜?gòu)會(huì)增大管理權(quán)限的復(fù)雜度，因此不建議使用。 對(duì)象可以在架構(gòu)之間移動(dòng)，架構(gòu)所有權(quán)也可以在主體之間轉(zhuǎn)移。 可以在不影響架構(gòu)的情況下刪除數(shù)據(jù)庫(kù)用戶。

實(shí)現(xiàn)后向兼容性的內(nèi)置架構(gòu)

SQL Server 隨附 9 個(gè)預(yù)定義架構(gòu)，這些架構(gòu)的名稱與內(nèi)置數(shù)據(jù)庫(kù)用戶和角色的名稱相同：db_accessadmin、db_backupoperator、db_datareader、db_datawriter、db_ddladmin、db_denydatareader、db_denydatawriter、db_owner 和 db_securityadmin。 這些架構(gòu)用于實(shí)現(xiàn)后向兼容性。 建議不要將它們用于用戶對(duì)象。 可以刪除與固定數(shù)據(jù)庫(kù)角色同名的架構(gòu) - 除非它們已被使用，在這種情況下，drop-command 僅返回錯(cuò)誤并阻止刪除已使用的架構(gòu)。 例如：

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]

如果從模型數(shù)據(jù)庫(kù)中刪除這些架構(gòu)，它們將不會(huì)顯示在新數(shù)據(jù)庫(kù)中。 不能刪除包含對(duì)象的架構(gòu)。

無(wú)法刪除以下架構(gòu)：

• dbo
• guest
• sys
• INFORMATION_SCHEMA

sys 和 INFORMATION_SCHEMA 架構(gòu)是為系統(tǒng)對(duì)象而保留的。 您不能在這些架構(gòu)中創(chuàng)建對(duì)象，而且不能刪除它們。

dbo 架構(gòu)

dbo 架構(gòu)是每個(gè)數(shù)據(jù)庫(kù)的默認(rèn)架構(gòu)。 默認(rèn)情況下，使用 CREATE USER Transact-SQL 命令創(chuàng)建的用戶的默認(rèn)架構(gòu)為 dbo。 dbo 架構(gòu)由 dbo 用戶帳戶擁有。

默認(rèn)架構(gòu)被分配為 dbo 的用戶不會(huì)繼承 dbo 用戶帳戶的權(quán)限。 用戶不從架構(gòu)繼承權(quán)限；架構(gòu)權(quán)限由架構(gòu)中包含的數(shù)據(jù)庫(kù)對(duì)象繼承。 用戶的默認(rèn)架構(gòu)僅用于對(duì)象引用，以防用戶在查詢對(duì)象時(shí)省略架構(gòu)。

當(dāng)使用部分名稱來(lái)引用數(shù)據(jù)庫(kù)對(duì)象時(shí)，SQL Server 首先在用戶的默認(rèn)架構(gòu)中查找。 如果在此處未找到該對(duì)象，則 SQL Server 其次將在 dbo 架構(gòu)中查找。 如果對(duì)象不在 dbo 架構(gòu)中，則會(huì)返回一個(gè)錯(cuò)誤。

參考鏈接：SQL Server 中所有權(quán)和用戶與架構(gòu)的分離 - SQL Server | Microsoft Learn

總結(jié)

到此這篇關(guān)于SQL Server跨庫(kù)/服務(wù)器查詢及拓展知識(shí)點(diǎn)的文章就介紹到這了,更多相關(guān)SQLServer跨庫(kù)/服務(wù)器查詢內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論