阿克苏地区建设局网站,房产网站建设价格,网站源码下载后怎么布置,增城网站建设价格这里写目录标题 1 SQL Server 跨库/服务器查询1.1 跨库查询1.2 跨服务器查询1.2.1 创建链接服务器1.2.2 跨库查询 1.3 拓展#xff1a;SQL Server 中所有权和用户与架构的分离 1 SQL Server 跨库/服务器查询
1.1 跨库查询
在同一服务器下的跨库查询较为简单#xff0c;示例… 这里写目录标题 1 SQL Server 跨库/服务器查询1.1 跨库查询1.2 跨服务器查询1.2.1 创建链接服务器1.2.2 跨库查询 1.3 拓展SQL Server 中所有权和用户与架构的分离 1 SQL Server 跨库/服务器查询
1.1 跨库查询
在同一服务器下的跨库查询较为简单示例
Database.DatabaseSchema.DatabaseObject
# 示例 [SqlMatc] 数据库中查询数据库[SQLATM]里的 DeptSales_copy 表里的所有数据
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身份。)
参数解读
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 提供程序建立连接时要使用的目录。
链接示例
if exists(select * from sys.servers where nameLinkedServerName)
begin--删除运行本地与远程之间的用户映射execute sys.sp_droplinkedsrvlogin rmtsrvnameLinkedServerName, localloginnull--删除链接服务器execute sys.sp_dropserver serverLinkedServerName, droploginsdroplogins
end
goEXEC sp_addlinkedserverserver LinkedServerName -- 目的服务器别名,srvproduct ,provider MSOLEDBSQL ,datasrc 192.168.3.21 -- 目的服务器IP地址,location -- 本地登录,provstr -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。--catalog ; -- 指定目录 默认值为 NULL-- 将凭据和选项添加到此链接服务器。
EXEC sp_addlinkedsrvloginrmtsrvname 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; 此处将链接SQL Server服务器封装成了名为RemoteConnectionServer的存储过程方便使用
在此存储过程中需要提供四个参数目的服务器别名见名知义、目的服务器IP、目的服务器登录名以及密码。即可创建一个服务器链接。注意此存储过程只创建了一个简单的链接服务器设置了必要的链接参数使用时请确保参数够用。
create proc RemoteConnectionServerLinkedServerName 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, localloginnull--删除链接服务器execute sys.sp_dropserver serverLinkedServerName, droploginsdroploginsend-- 创建链接服务器 exec sp_addlinkedserverserver LinkedServerName -- 目的服务器别名,srvproduct ,provider MSOLEDBSQL ,datasrc LinkedServerIP -- 目的服务器IP地址,location -- 本地登录,provstr -- 标识唯一数据源的特定于 OLE DB 提供程序的连接字符串。--catalog ; -- 指定目录 默认值为 NULL-- 将凭据和选项添加到此链接服务器。exec sp_addlinkedsrvloginrmtsrvname LinkedServerName,useself false -- 是否通过模拟登录 or 显示的提交登录名和密码链接到远程服务器,rmtuser userName -- 登录名,rmtpassword password; -- 密码-- 设置服务器选项exec sp_serveroption LinkedServerName, rpc, true; -- 从指定的服务器启用远程过程调用 (RPC)exec sp_serveroption LinkedServerName, rpc out, true; -- 对指定的服务器启用 RPC。go执行存储过程
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; 方法二利用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
参考链接01sp_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.3 拓展SQL Server 中所有权和用户与架构的分离
SQL Server 安全性的核心概念是对象的所有者具有管理这些对象的不可撤消的权限。 你不能删除对象所有者的特权并且如果用户在数据库中拥有对象你也不能将用户从此数据库中删除。
用户架构分离
通过用户架构分离可实现管理数据库对象权限的更大灵活性。 架构是一个适用于数据库对象的命名容器它使你能够将对象分组到单独的命名空间中。
用于引用对象的由四部分组成的命名语法指定架构名称。
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 仅返回错误并阻止删除已使用的架构。 例如
IF EXISTS (SELECT * FROM sys.schemas WHERE name Ndb_accessadmin)
DROP SCHEMA [db_accessadmin]
GOIF EXISTS (SELECT * FROM sys.schemas WHERE name Ndb_backupoperator)
DROP SCHEMA [db_backupoperator]如果从模型数据库中删除这些架构它们将不会显示在新数据库中。 不能删除包含对象的架构。
无法删除以下架构
dboguestsysINFORMATION_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
