SQL?Server跨庫/服務(wù)器查詢及拓展知識點
1 SQL Server 跨庫/服務(wù)器查詢
1.1 跨庫查詢
在同一服務(wù)器下的跨庫查詢較為簡單,示例
Database.DatabaseSchema.DatabaseObject # 示例 [SqlMatc] 數(shù)據(jù)庫中,查詢數(shù)據(jù)庫[SQLATM]里的 DeptSales_copy 表里的所有數(shù)據(jù) select * from [SQLATM].[dbo].[DeptSales_copy]
1.2 跨服務(wù)器查詢
進行跨服務(wù)器查詢前提是在本地服務(wù)器上創(chuàng)建鏈接服務(wù)器。
1.2.1 創(chuàng)建鏈接服務(wù)器
方法一:利用系統(tǒng)存儲過程 sp_addlinkedserver
sp_addlinkedserver
創(chuàng)建鏈接服務(wù)器。 鏈接服務(wù)器提供對 OLE DB 數(shù)據(jù)源的分布式異類查詢的訪問權(quán)限。 使用 sp_addlinkedserver
創(chuàng)建鏈接服務(wù)器后,可對此服務(wù)器運行分布式查詢。 如果將鏈接服務(wù)器定義為 SQL Server 實例,則可以執(zhí)行遠程存儲過程。
權(quán)限
語句 sp_addlinkedserver
需要 ALTER ANY LINKED SERVER
權(quán)限。 (“SQL Server Management Studio新建鏈接服務(wù)器
”對話框的實現(xiàn)方式需要固定服務(wù)器角色的成員sysadmin
身份。)
參數(shù)解讀
sp_addlinkedserver [ @server = ] 'server' -- 鏈接服務(wù)器的名稱 [ , [ @srvproduct = ] 'product_name' ] -- 鏈接服務(wù)器的 OLE DB 數(shù)據(jù)源的產(chǎn)品名 -- product_name值為 nvarchar (128) , -- 默認值為 NULL。 -- 如果值為SQL Server,則無需指provider_name、 -- data_source、位置、provider_string和目錄。 [ , [ @provider = ] 'provider_name' ] -- 唯一編程標識符。建議使用 MSOLEDBSQL 而不是 SQLNCLI。 [ , [ @datasrc = ] 'data_source' ] -- 目的服務(wù)器地址 [ , [ @location = ] 'location' ] -- 本地登錄 [ , [ @provstr = ] 'provider_string' ] -- 標識唯一數(shù)據(jù)源的特定于 OLE DB 提供程序的連接字符串。 [ , [ @catalog = ] 'catalog' ] -- 與 OLE DB 提供程序建立連接時要使用的目錄。
鏈接示例:
if exists(select * from sys.servers where name='LinkedServerName') begin --刪除運行本地與遠程之間的用戶映射 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 = '' -- 標識唯一數(shù)據(jù)源的特定于 OLE DB 提供程序的連接字符串。 --@catalog = ''; -- 指定目錄 默認值為 NULL -- 將憑據(jù)和選項添加到此鏈接服務(wù)器。 EXEC sp_addlinkedsrvlogin @rmtsrvname = 'LinkedServerName' ,@useself = 'false' -- 是否通過模擬登錄 or 顯示的提交登錄名和密碼鏈接到遠程服務(wù)器 ,@rmtuser = 'sa' -- 登錄名 ,@rmtpassword = 'root'; -- 密碼 EXEC sp_serveroption 'LinkedServerName', 'rpc', true; -- 從指定的服務(wù)器啟用遠程過程調(diào)用 (RPC) EXEC sp_serveroption 'LinkedServerName', 'rpc out', true; -- 對指定的服務(wù)器啟用 RPC。 -- 查詢示例 select * from Server.Database.DatabaseSchema.DatabaseObject SELECT name FROM [LinkedServerName].master.sys.databases;
此處將鏈接SQL Server服務(wù)器封裝成了名為RemoteConnectionServer的存儲過程方便使用
在此存儲過程中需要提供四個參數(shù),目的服務(wù)器別名(見名知義)、目的服務(wù)器IP、目的服務(wù)器登錄名以及密碼。即可創(chuàng)建一個服務(wù)器鏈接。(注意此存儲過程只創(chuàng)建了一個簡單的鏈接服務(wù)器,設(shè)置了必要的鏈接參數(shù),使用時請確保參數(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 -- 刪除運行本地與遠程之間的用戶映射 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 = '' -- 標識唯一數(shù)據(jù)源的特定于 OLE DB 提供程序的連接字符串。 --@catalog = ''; -- 指定目錄 默認值為 NULL -- 將憑據(jù)和選項添加到此鏈接服務(wù)器。 exec sp_addlinkedsrvlogin @rmtsrvname = @LinkedServerName ,@useself = 'false' -- 是否通過模擬登錄 or 顯示的提交登錄名和密碼鏈接到遠程服務(wù)器 ,@rmtuser = @userName -- 登錄名 ,@rmtpassword = @password; -- 密碼 -- 設(shè)置服務(wù)器選項 exec sp_serveroption @LinkedServerName, 'rpc', true; -- 從指定的服務(wù)器啟用遠程過程調(diào)用 (RPC) exec sp_serveroption @LinkedServerName, 'rpc out', true; -- 對指定的服務(wù)器啟用 RPC。 go
執(zhí)行存儲過程
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 -- 測試 SELECT name FROM linkName.master.sys.databases;
方法二:利用SSMS創(chuàng)建鏈接服務(wù)器
下面以鏈接192.168.2.21 SQL Server 服務(wù)器為例:
1 打開SSMS鏈接到本地服務(wù)器
2 選擇服務(wù)器對象–>鏈接服務(wù)器
3 鏈接服務(wù)器 右鍵 新建鏈接服務(wù)器 常規(guī)頁
PS:如果勾選服務(wù)器類型為"SQL Server",則此處’‘鏈接服務(wù)器’'名必須為IP 地址。若選擇"其他數(shù)據(jù)源",此處的鏈接服務(wù)器名作為映射存在(別名), 在數(shù)據(jù)源處填寫IP地址。
當然你也可以選擇其他數(shù)據(jù)源去鏈接 SQL Server 服務(wù)器。
4 選擇“安全性”頁
5 選擇 “服務(wù)器選項” 將 RPC 與 RPC Out 設(shè)置為True,默認為False。
- RPC
從指定的服務(wù)器啟用遠程過程調(diào)用 (RPC)。 - RPC Out
對指定的服務(wù)器啟用 RPC。
6 點擊 “確定”,此時鏈接服務(wù)器目錄下會出現(xiàn)你創(chuàng)建的連接服務(wù)器。
7 右鍵 測試鏈接
successs!
參考鏈接01:sp_addlinkedserver (Transact-SQL) - SQL Server | Microsoft Learn
參考鏈接02:創(chuàng)建鏈接服務(wù)器 - SQL Server | Microsoft Learn
1.2.2 跨庫查詢
-- 查詢示例 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 安全性的核心概念是對象的所有者具有管理這些對象的不可撤消的權(quán)限。 你不能刪除對象所有者的特權(quán),并且如果用戶在數(shù)據(jù)庫中擁有對象,你也不能將用戶從此數(shù)據(jù)庫中刪除。
用戶架構(gòu)分離
通過用戶架構(gòu)分離,可實現(xiàn)管理數(shù)據(jù)庫對象權(quán)限的更大靈活性。 架構(gòu)是一個適用于數(shù)據(jù)庫對象的命名容器,它使你能夠?qū)ο蠓纸M到單獨的命名空間中。
用于引用對象的由四部分組成的命名語法指定架構(gòu)名稱。
Server.Database.DatabaseSchema.DatabaseObject
架構(gòu)所有者和權(quán)限
任何數(shù)據(jù)庫主體都可以擁有架構(gòu),并且一個主體可擁有多個架構(gòu)。 您可以對架構(gòu)應(yīng)用安全規(guī)則,安全規(guī)則將由架構(gòu)中的所有對象繼承。 如果設(shè)置了對架構(gòu)的訪問權(quán)限,則當新對象添加到架構(gòu)時,新對象會自動應(yīng)用這些權(quán)限。 可以為用戶分配一個默認的架構(gòu),且多個數(shù)據(jù)庫用戶可以共享同一架構(gòu)。
默認情況下,當開發(fā)人員在架構(gòu)中創(chuàng)建對象時,該對象由擁有架構(gòu)的安全主體而不是開發(fā)人員擁有。 可以使用 ALTER AUTHORIZATION Transact-SQL 語句轉(zhuǎn)移對象所有權(quán)。 盡管架構(gòu)還可以包含由不同用戶擁有的對象并且這些對象具有比分配給架構(gòu)的權(quán)限更加細化的權(quán)限,但因為架構(gòu)會增大管理權(quán)限的復(fù)雜度,因此不建議使用。 對象可以在架構(gòu)之間移動,架構(gòu)所有權(quán)也可以在主體之間轉(zhuǎn)移。 可以在不影響架構(gòu)的情況下刪除數(shù)據(jù)庫用戶。
實現(xiàn)后向兼容性的內(nèi)置架構(gòu)
SQL Server 隨附 9 個預(yù)定義架構(gòu),這些架構(gòu)的名稱與內(nèi)置數(shù)據(jù)庫用戶和角色的名稱相同:db_accessadmin、db_backupoperator、db_datareader、db_datawriter、db_ddladmin、db_denydatareader、db_denydatawriter、db_owner 和 db_securityadmin。 這些架構(gòu)用于實現(xiàn)后向兼容性。 建議不要將它們用于用戶對象。 可以刪除與固定數(shù)據(jù)庫角色同名的架構(gòu) - 除非它們已被使用,在這種情況下,drop-command 僅返回錯誤并阻止刪除已使用的架構(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ù)庫中刪除這些架構(gòu),它們將不會顯示在新數(shù)據(jù)庫中。 不能刪除包含對象的架構(gòu)。
無法刪除以下架構(gòu):
dbo
guest
sys
INFORMATION_SCHEMA
sys
和 INFORMATION_SCHEMA
架構(gòu)是為系統(tǒng)對象而保留的。 您不能在這些架構(gòu)中創(chuàng)建對象,而且不能刪除它們。
dbo 架構(gòu)
dbo
架構(gòu)是每個數(shù)據(jù)庫的默認架構(gòu)。 默認情況下,使用 CREATE USER Transact-SQL 命令創(chuàng)建的用戶的默認架構(gòu)為 dbo
。 dbo
架構(gòu)由 dbo
用戶帳戶擁有。
默認架構(gòu)被分配為 dbo
的用戶不會繼承 dbo
用戶帳戶的權(quán)限。 用戶不從架構(gòu)繼承權(quán)限;架構(gòu)權(quán)限由架構(gòu)中包含的數(shù)據(jù)庫對象繼承。 用戶的默認架構(gòu)僅用于對象引用,以防用戶在查詢對象時省略架構(gòu)。
當使用部分名稱來引用數(shù)據(jù)庫對象時,SQL Server 首先在用戶的默認架構(gòu)中查找。 如果在此處未找到該對象,則 SQL Server 其次將在 dbo
架構(gòu)中查找。 如果對象不在 dbo
架構(gòu)中,則會返回一個錯誤。
參考鏈接:SQL Server 中所有權(quán)和用戶與架構(gòu)的分離 - SQL Server | Microsoft Learn
總結(jié)
到此這篇關(guān)于SQL Server跨庫/服務(wù)器查詢及拓展知識點的文章就介紹到這了,更多相關(guān)SQLServer跨庫/服務(wù)器查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL 和 SQL Server之間的數(shù)據(jù)遷移方法小結(jié)
遷移MySQL數(shù)據(jù)到SQL Server可以通過多種方法實現(xiàn),本文主要介紹了MySQL 和 SQL Server之間的數(shù)據(jù)遷移方法小結(jié),使用 SSMS和SQL SSIS工具,感興趣的可以了解一下2024-04-04有關(guān)數(shù)據(jù)庫SQL遞歸查詢在不同數(shù)據(jù)庫中的實現(xiàn)方法
這篇文章主要介紹了有關(guān)數(shù)據(jù)庫SQL遞歸查詢在不同數(shù)據(jù)庫中的實現(xiàn)方法的相關(guān)資料,需要的朋友可以參考下2015-10-10SqlServer 2022通過臨時表和游標遍歷方式邏輯處理獲取目標數(shù)據(jù)
在SQL的存儲過程,函數(shù)中,經(jīng)常需要使用遍歷(遍歷table),其中游標、臨時表等遍歷方法很常用,本文就來介紹一下SqlServer 2022通過臨時表和游標遍歷方式邏輯處理獲取目標數(shù)據(jù),感興趣的可以了解一下2024-04-04win2008 r2 安裝sql server 2005/2008 無法連接服務(wù)器解決方法
在與 SQL Server 建立連接時出現(xiàn)與網(wǎng)絡(luò)相關(guān)的或特定于實例的錯誤。未找到或無法訪問服務(wù)器。請驗證實例名稱是否正確并且 SQL Server 已配置為允許遠程連接2015-01-01sql server數(shù)據(jù)庫高可用日志傳送的方法
SQL Server 使用日志傳送,您可以自動將“主服務(wù)器”實例上“主數(shù)據(jù)庫”內(nèi)的事務(wù)日志備份發(fā)送到單獨“輔助服務(wù)器”實例上的一個或多個“輔助數(shù)據(jù)庫”,下面這篇文章主要給大家介紹了關(guān)于sql server數(shù)據(jù)庫高可用日志傳送的相關(guān)資料,需要的朋友可以參考下2018-05-05SQL?Server使用T-SQL進階之公用表表達式(CTE)
這篇文章介紹了SQL?Server中T-SQL的公用表表達式(CTE),文中通過示例代碼介紹的非常詳細。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2022-05-05