修改数据库架构注意事项
用户与架构完全分离。
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、连接服务器 -》展开数据库文件夹 -》选择数据库并展开 -》展开安全性文件夹 -》展开架构文件夹 -》选择要修改的架构右键点击属性。
2、在架构属性弹出框 -》点击常规 -》点击搜索修改架构所有者。
3、在架构属性弹出框 -》点击权限 -》点击搜索选择用户或角色 -》选择用户或角色权限。
4、在架构属性弹出框 -》点击扩展属性 -》新增或者删除扩展属性。
使用 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 脚本需要刷新表文件夹才能查看执行结果。