SQLServer之修改数据库架构
修改数据库架构注意事项
用户与架构完全分离。
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脚本需要刷新表文件夹才能查看执行结果。