SQL?Server跨庫(kù)/服務(wù)器查詢及拓展知識(shí)點(diǎn)
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)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL 和 SQL Server之間的數(shù)據(jù)遷移方法小結(jié)
遷移MySQL數(shù)據(jù)到SQL Server可以通過多種方法實(shí)現(xiàn),本文主要介紹了MySQL 和 SQL Server之間的數(shù)據(jù)遷移方法小結(jié),使用 SSMS和SQL SSIS工具,感興趣的可以了解一下2024-04-04有關(guān)數(shù)據(jù)庫(kù)SQL遞歸查詢?cè)诓煌瑪?shù)據(jù)庫(kù)中的實(shí)現(xiàn)方法
這篇文章主要介紹了有關(guān)數(shù)據(jù)庫(kù)SQL遞歸查詢?cè)诓煌瑪?shù)據(jù)庫(kù)中的實(shí)現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2015-10-10利用sql函數(shù)生成不重復(fù)的訂單號(hào)的代碼
利用sql函數(shù)生成不重復(fù)的訂單號(hào)的代碼,需要的朋友可以參考下。2011-12-12SqlServer 2022通過臨時(shí)表和游標(biāo)遍歷方式邏輯處理獲取目標(biāo)數(shù)據(jù)
在SQL的存儲(chǔ)過程,函數(shù)中,經(jīng)常需要使用遍歷(遍歷table),其中游標(biāo)、臨時(shí)表等遍歷方法很常用,本文就來(lái)介紹一下SqlServer 2022通過臨時(shí)表和游標(biāo)遍歷方式邏輯處理獲取目標(biāo)數(shù)據(jù),感興趣的可以了解一下2024-04-04win2008 r2 安裝sql server 2005/2008 無(wú)法連接服務(wù)器解決方法
在與 SQL Server 建立連接時(shí)出現(xiàn)與網(wǎng)絡(luò)相關(guān)的或特定于實(shí)例的錯(cuò)誤。未找到或無(wú)法訪問服務(wù)器。請(qǐng)驗(yàn)證實(shí)例名稱是否正確并且 SQL Server 已配置為允許遠(yuǎn)程連接2015-01-01sql server數(shù)據(jù)庫(kù)高可用日志傳送的方法
SQL Server 使用日志傳送,您可以自動(dòng)將“主服務(wù)器”實(shí)例上“主數(shù)據(jù)庫(kù)”內(nèi)的事務(wù)日志備份發(fā)送到單獨(dú)“輔助服務(wù)器”實(shí)例上的一個(gè)或多個(gè)“輔助數(shù)據(jù)庫(kù)”,下面這篇文章主要給大家介紹了關(guān)于sql server數(shù)據(jù)庫(kù)高可用日志傳送的相關(guān)資料,需要的朋友可以參考下2018-05-05SQL?Server使用T-SQL進(jìn)階之公用表表達(dá)式(CTE)
這篇文章介紹了SQL?Server中T-SQL的公用表表達(dá)式(CTE),文中通過示例代碼介紹的非常詳細(xì)。對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下2022-05-05sql2000 卸載后重新安裝時(shí)不能安裝的解決辦法
sql2000卸載了后重新安裝時(shí)不能安裝的解決辦法,大家可以看下,下面的步驟。2009-07-07