欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQLServer之修改数据库架构

程序员文章站 2022-07-28 14:48:43
修改数据库架构注意事项 用户与架构完全分离。 ALTER SCHEMA 仅可用于在同一数据库中的架构之间移动安全对象。 若要更改或删除架构中的安全对象,请使用特定于该安全对象的 ALTER 或 DROP 语句。 如果对 securable_name 使用了由一部分组成的名称,则将使用当前生效的名称解 ......

修改数据库架构注意事项

用户与架构完全分离。

alter schema 仅可用于在同一数据库中的架构之间移动安全对象。 若要更改或删除架构中的安全对象,请使用特定于该安全对象的 alter 或 drop 语句。

如果对 securable_name 使用了由一部分组成的名称,则将使用当前生效的名称解析规则查找该安全对象。

将安全对象移入新架构时,将删除与该安全对象关联的全部权限。 如果已显式设置安全对象的所有者,则该所有者保持不变。 如果安全对象的所有者已设置为 schema owner,则该所有者将保持为 schema owner;但移动之后,schema owner 将解析为新架构的所有者。 新所有者的 principal_id 将为 null。

无论是 sys.sql_modules 目录视图的 definition 列中的相应对象,还是使用 object_definition 内置函数获取的相应对象,移动存储过程、函数、视图或触发器都不会更改其架构名称(如有)。 因此,我们建议不要使用 alter schema 移动这些对象类型。 而是删除对象,然后在新架构中重新创建该对象。

移动表或同义词不会自动更新对该对象的引用。 必须手动修改引用已移动对象的任何对象。 例如,如果移动了某个表,并且触发器中引用了该表,则必须修改触发器以反映新的架构名称。 请使用 sys.sql_expression_dependencies 列出该对象上的依赖关系,然后再进行移动。

若要通过使用 sql server management studio 更改表的架构,请在对象资源管理器中右键单击该表,然后单击“设计”。 按 f4 以打开“属性”窗口。 在“架构”框中,选择新架构。

若要从另一个架构中传输安全对象,当前用户必须拥有对该安全对象(非架构)的 control 权限,并拥有对目标架构的 alter 权限。

如果已为安全对象指定 execute as owner,且所有者已设置为 schema owner,则用户还必须拥有对目标架构所有者的 impersonate 权限。

在移动安全对象后,将删除与所传输的安全对象相关联的所有权限。

使用ssms数据库管理工具修改架构

1、连接服务器-》展开数据库文件夹-》选择数据库并展开-》展开安全性文件夹-》展开架构文件夹-》选择要修改的架构右键点击属性。

SQLServer之修改数据库架构

2、在架构属性弹出框-》点击常规-》点击搜索修改架构所有者。

SQLServer之修改数据库架构

3、在架构属性弹出框-》点击权限-》点击搜索选择用户或角色-》选择用户或角色权限。

SQLServer之修改数据库架构

4、在架构属性弹出框-》点击扩展属性-》新增或者删除扩展属性。

SQLServer之修改数据库架构

使用t-sql脚本修改数据库架构

语法

--声明数据库引用
use database_name;
go

修改用户或者角色
alter authorization on schema::[architecturename] to [schemaowner];
go

--修改用户或角色权限
--授予插入
grant insert on schema::[architecturename] to [rolename_username];
go

--授予查看定义
grant view definition on schema::[architecturename] to [rolename_username];
go

--授予查看更改跟踪
grant view change tracking on schema::[architecturename] to [rolename_username];
go

--授予创建序列
grant create sequence on schema::[architecturename] to [rolename_username];
go

--授予更改
grant alter on schema::[architecturename] to [rolename_username];
go
 
 --授予更新
grant update on schema::[architecturename] to [rolename_username];
go

--接管所有权
grant take ownership on schema::[architecturename] to [rolename_username];
go

--授予控制
grant control on schema::[architecturename] to [rolename_username];
go

--授予删除
grant delete on schema::[architecturename] to [rolename_username];
go

--授予选择
grant select on schema::[architecturename] to [rolename_username];
go

--授予引用
grant references on schema::[architecturename] to [rolename_username];
go

--授予执行
grant execute on schema::[architecturename] to [rolename_username];
go

--授予并允许转授插入
grant insert on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授查看定义
grant view definition on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授查看更改跟踪
grant view change tracking on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授创建序列
grant create sequence on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授更改
grant alter on schema::[architecturename] to [rolename_username] with grant option;
go
 
 --授予并允许转授更新
grant update on schema::[architecturename] to [rolename_username] with grant option;
go

--接管并允许转授所有权
grant take ownership on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授控制
grant control on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授删除
grant delete on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授选择
grant select on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授引用
grant references on schema::[architecturename] to [rolename_username] with grant option;
go

--授予并允许转授执行
grant execute on schema::[architecturename] to [rolename_username] with grant option;
go

--拒绝插入
deny insert on schema::[architecturename] to [rolename_username];
go

--拒绝查看定义
deny view definition on schema::[architecturename] to [rolename_username];
go

--拒绝查看更改跟踪
deny view change tracking on schema::[architecturename] to [rolename_username];
go

--拒绝创建序列
deny create sequence on schema::[architecturename] to [rolename_username];
go

--拒绝更改
deny alter on schema::[architecturename] to [rolename_username];
go
 
--拒绝更新
deny update on schema::[architecturename] to [rolename_username];
go

--拒绝所有权
deny take ownership on schema::[architecturename] to [rolename_username];
go

--拒绝控制
deny control on schema::[architecturename] to [rolename_username];
go

--拒绝删除
deny delete on schema::[architecturename] to [rolename_username];
go

--拒绝选择
deny select on schema::[architecturename] to [rolename_username];
go

--拒绝引用
deny references on schema::[architecturename] to [rolename_username];
go

--拒绝执行
deny execute on schema::[architecturename] to [rolename_username];
go

删除数据库架构扩展属性
exec sys.sp_dropextendedproperty @name=n'extendedattributename',@level0type=n'schema',@level0name=n'extendedattributevalue'
go

创建数据库架构扩属性
exec sys.sp_addextendedproperty @name=n'newextendedattributename',@value=n'newextendedattributevalue' , @level0type=n'schema',@level0name=n'architecturename'
go

--修改数据库架构
alter schema schema_name(你要修改成得新架构)
transfer { object | type | xml schema collection } securable_name (原架构名.对象名);
go   

语法解析

--语法解析
--schema_name
--当前数据库中的架构名称,安全对象将移入其中。其数据类型不能为sys或information_schema。

--architecturename
--架构名称

--schemaowner
--架构所有者

--rolename_username
--用户或角色

--extendedattributename
--要删除的扩展属性名称

--extendedattributevalue
--要删除的扩展属性值

--newextendedattributename
--新添加扩展属性名称

--newextendedattributevalue
--新添加的扩展属性值

--transfer { object | type | xml schema collection }
--更改其所有者的实体的类。object是默认值。

--securable_name
--要移入架构中的架构范围内的安全对象的一部分或两部分名称。

示例

--声明数据库引用
use [testss];
go						   

--修改数据库架构
--修改架构所有者
alter authorization on schema::[testarchitecture] to [db_datareader];
go

--修改用户或角色权限
--授予插入
grant insert on schema::[testarchitecture] to [guest];
go

--授予查看定义
grant view definition on schema::[testarchitecture] to [guest];
go

--授予查看更改跟踪
grant view change tracking on schema::[testarchitecture] to [guest];
go

--授予创建序列
grant create sequence on schema::[testarchitecture] to [guest];
go

--授予更改
grant alter on schema::[testarchitecture] to [guest];
go
 
 --授予更新
grant update on schema::[testarchitecture] to [guest];
go

--接管所有权
grant take ownership on schema::[testarchitecture] to [guest];
go

--授予控制
grant control on schema::[testarchitecture] to [guest];
go

--授予删除
grant delete on schema::[testarchitecture] to [guest];
go

--授予选择
grant select on schema::[testarchitecture] to [guest];
go

--授予引用
grant references on schema::[testarchitecture] to [guest];
go

--授予执行
grant execute on schema::[testarchitecture] to [guest];
go

----授予并允许转授插入
--grant insert on schema::[testarchitecture] to [[guest]] with grant option;
--go

----授予并允许转授查看定义
--grant view definition on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授查看更改跟踪
--grant view change tracking on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授创建序列
--grant create sequence on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授更改
--grant alter on schema::[testarchitecture] to [guest] with grant option;
--go
 
-- --授予并允许转授更新
--grant update on schema::[testarchitecture] to [guest] with grant option;
--go

----接管并允许转授所有权
--grant take ownership on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授控制
--grant control on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授删除
--grant delete on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授选择
--grant select on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授引用
--grant references on schema::[testarchitecture] to [guest] with grant option;
--go

----授予并允许转授执行
--grant execute on schema::[testarchitecture] to [guest] with grant option;
--go

----拒绝插入
--deny insert on schema::[testarchitecture] to [guest];
--go

----拒绝查看定义
--deny view definition on schema::[testarchitecture] to [guest];
--go

----拒绝查看更改跟踪
--deny view change tracking on schema::[testarchitecture] to [guest];
--go

----拒绝创建序列
--deny create sequence on schema::[testarchitecture] to [guest];
--go

----拒绝更改
--deny alter on schema::[testarchitecture] to [guest];
--go
 
----拒绝更新
--deny update on schema::[testarchitecture] to [guest];
--go

----拒绝所有权
--deny take ownership on schema::[testarchitecture] to [guest];
--go

----拒绝控制
--deny control on schema::[testarchitecture] to [guest];
--go

----拒绝删除
--deny delete on schema::[testarchitecture] to [guest];
--go

----拒绝选择
--deny select on schema::[testarchitecture] to [guest];
--go

----拒绝引用
--deny references on schema::[testarchitecture] to [guest];
--go

----拒绝执行
--deny execute on schema::[testarchitecture] to [guest];
--go

--删除数据库架构扩展属性
exec sys.sp_dropextendedproperty @name=n'testcrituer' , @level0type=n'schema',@level0name=n'testarchitecture'
go

--创建数据库架构扩属性
exec sys.sp_addextendedproperty @name=n'testcrituer', @value=n'测试创建数据库架构' , @level0type=n'schema',@level0name=n'testarchitecture'
go

--修改架构下对象所有权,从[testarchitecture]转移到[dbo]
alter schema [dbo] transfer [testarchitecture].[schema_table1];
go

示例结果:执行t-sql脚本需要刷新表文件夹才能查看执行结果。

SQLServer之修改数据库架构