SQLServer之创建数据库架构

Myth丶恋晨 2022-03-02 04:39 633阅读 0赞

# 创建数据库架构注意事项 #

包含 CREATE SCHEMA AUTHORIZATION 但未指定名称的语句仅允许用于向后兼容性。 该语句未引起错误，但未创建一个架构。

CREATE SCHEMA 可以在单条语句中创建架构以及该架构所包含的表和视图，并授予对任何安全对象的 GRANT、REVOKE 或 DENY 权限。 此语句必须作为一个单独的批处理执行。 CREATE SCHEMA 语句所创建的对象将在要创建的架构内进行创建。

CREATE SCHEMA 事务是原子级的。 如果 CREATE SCHEMA 语句执行期间出现任何错误，则不会创建任何指定的安全对象，也不会授予任何权限。

由 CREATE SCHEMA 创建的安全对象可以任何顺序列出，但引用其他视图的视图除外。 在这种情况下，被引用的视图必须在引用它的视图之前创建。

因此，GRANT 语句可以在创建某个对象自身之前对该对象授予权限，CREATE VIEW 语句也可以出现在创建该视图所引用表的 CREATE TABLE 语句之前。 同样，CREATE TABLE 语句可以在 CREATE SCHEMA 语句定义表之前声明表的外键。

执行 CREATE SCHEMA 的主体可以将另一个数据库主体指定为要创建的架构的所有者。 完成此操作需要另外的权限，如本主题下文中的“权限”部分所述。

新架构由以下数据库级别主体之一拥有：数据库用户、数据库角色或应用程序角色。 在架构内创建的对象由架构所有者拥有，这些对象在 sys.objects 中的 principal\_id为 NULL。 架构所包含对象的所有权可转让给任何数据库级主体，但架构所有者始终保留对该架构内对象的 CONTROL 权限。

隐式架构和用户创建

在某些情况下，用户可在没有数据库用户帐户（数据库中的数据库主体）的情况下使用数据库。 这可发生在以下情况中：

登录名具有 CONTROL SERVER 特权。

Windows 用户没有单独的数据库用户帐户（数据库中的数据库主体），但以具有数据库用户帐户（Windows 组的数据库主体）的 Windows 组成员的身份访问数据库。

如果没有数据库用户帐户的用户在不指定现有架构的情况下创建对象，则将在数据库中自动为该用户创建数据库主体和默认架构。 创建的数据库主体和架构采用的名称将与连接到 SQL Server 时用户使用的名称（ SQL Server 身份验证登录名或 Windows 用户名）相同。

若要允许基于 Windows 组的用户创建和拥有对象，此行为很有必要。 但这种行为可能将导致意外创建架构和用户。 为了避免隐式创建用户和架构，请尽可能显式创建数据库主体和分配默认架构。 或者，在数据库中创建对象时，使用由两部分或三部分组成的对象名称显式声明现有架构。

当前支持不指定架构名称的 CREATE SCHEMA 语句，目的是为了向后兼容。 此类语句并不在数据库中实际创建架构，但它们会创建表和视图，并授予权限。 主体不需要 CREATE SCHEMA 权限来执行这一早期形式的 CREATE SCHEMA，因为不会创建任何架构。 此功能将从 SQL Server 的未来版本中删除。

需要对数据库拥有 CREATE SCHEMA 权限。

若要创建在 CREATE SCHEMA 语句中指定的对象，用户必须拥有相应的 CREATE 权限。

若要指定其他用户作为所创建架构的所有者，则调用方必须具有对该用户的 IMPERSONATE 权限。 如果指定一个数据库角色作为所有者，则调用方必须拥有该角色的成员身份或对该角色拥有 ALTER 权限。

# 使用SSMS数据库管理工具创建数据库架构 #

1、连接服务器-》展开数据库文件夹-》选择数据库并展开-》展开安全性-》展开架构-》右键单击架构文件夹选择创建架构。

![clipboard.png][]

2、在新建架构弹出框-》点击常规-》输入新建架构名称-》点击搜索选择架构所有者。

![clipboard.png][clipboard.png 1]

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

![clipboard.png][clipboard.png 2]

4、在新建架构弹出框-》点击扩展属性-》输入扩展属性名称和值-》点击确定。

![clipboard.png][clipboard.png 3]

5、不需要刷新即可在对象资源管理器中查看创建结果。

![clipboard.png][clipboard.png 4]

# 使用T-SQL脚本创建数据库架构 #

## 语法 ##

----声明数据库引用
    --use database_name;
    --go
     
    ----创建数据库架构
    --create schema schema_name authorization owner_name 
    --{ table_definition | view_definition | grant_statement | revoke_statement | deny_statement }
    --;
    --go

## 语法解析 ##

\--语法解析  
\--database\_name  
\--架构所在的数据库名

\--schema\_name  
\--在数据库内标识架构的名称。

\--authorization owner\_name  
\--指定将拥有架构的数据库级主体的名称。此主体还可以拥有其他架构，并且可以不使用当前架构作为其默认架构。

\--table\_definition  
\--指定在架构内创建表的CREATE TABLE语句。执行此语句的主体必须对当前数据库具有CREATE TABLE权限。

\--view\_definition  
\--指定在架构内创建视图的CREATE VIEW语句。执行此语句的主体必须对当前数据库具有CREATE VIEW权限。

\--grant\_statement  
\--指定可对除新架构外的任何安全对象授予权限的GRANT语句。

\--revoke\_statement  
\--指定可对除新架构外的任何安全对象撤消权限的REVOKE语句。

\--deny\_statement  
\--指定可对除新架构外的任何安全对象拒绝授予权限的DENY语句。

## 示例 ##

--声明数据库引用
    use [testss];
    go
     
    if exists(select * from sys.schemas where name='testarchitecture')
    --删除数据库架构注释
    exec sys.sp_dropextendedproperty @name=N'testcrituer' , @level0type=N'schema',@level0name=N'testarchitecture';
        --删除架构下的所有表
        if exists(select * from sys.tables where name='schema_table1')
        drop table [testarchitecture].[schema_table1];
        go
    --删除数据库架构
    drop schema testarchitecture; 
    go
     
    --创建数据库架构
    create schema [testarchitecture] authorization [db_accessadmin]
    create table schema_table1
    (
    id int identity(1,1) not null,
    name nvarchar(50),
    primary key clustered(id asc) with(ignore_dup_key=off) on [primary]
    )on [primary]
    go
     
     
    --授予插入
    grant insert on schema::[testarchitecture] to [public];
    go
     
    --授予查看定义
    grant view definition on schema::[testarchitecture] to [public];
    go
     
    --授予查看更改跟踪
    grant view change tracking on schema::[testarchitecture] to [public];
    go
     
    --授予创建序列
    grant create sequence on schema::[testarchitecture] to [public];
    go
     
    --授予更改
    grant alter on schema::[testarchitecture] to [public];
    go
      
     --授予更新
    grant update on schema::[testarchitecture] to [public];
    go
     
    --接管所有权
    grant take ownership on schema::[testarchitecture] to [public];
    go
     
    --授予控制
    grant control on schema::[testarchitecture] to [public];
    go
     
    --授予删除
    grant delete on schema::[testarchitecture] to [public];
    go
     
    --授予选择
    grant select on schema::[testarchitecture] to [public];
    go
     
    --授予引用
    grant references on schema::[testarchitecture] to [public];
    go
     
    --授予执行
    grant execute on schema::[testarchitecture] to [public];
    go
     
    ----授予并允许转授插入
    --grant insert on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授查看定义
    --grant view definition on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授查看更改跟踪
    --grant view change tracking on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授创建序列
    --grant create sequence on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授更改
    --grant alter on schema::[testarchitecture] to [public] with grant option;
    --go
      
    -- --授予并允许转授更新
    --grant update on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----接管并允许转授所有权
    --grant take ownership on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授控制
    --grant control on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授删除
    --grant delete on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授选择
    --grant select on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授引用
    --grant references on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----授予并允许转授执行
    --grant execute on schema::[testarchitecture] to [public] with grant option;
    --go
     
    ----拒绝插入
    --deny insert on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝查看定义
    --deny view definition on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝查看更改跟踪
    --deny view change tracking on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝创建序列
    --deny create sequence on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝更改
    --deny alter on schema::[testarchitecture] to [public];
    --go
      
    ----拒绝更新
    --deny update on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝所有权
    --deny take ownership on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝控制
    --deny control on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝删除
    --deny delete on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝选择
    --deny select on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝引用
    --deny references on schema::[testarchitecture] to [public];
    --go
     
    ----拒绝执行
    --deny execute on schema::[testarchitecture] to [public];
    --go
     
    --用户或者角色
    alter authorization on schema::[testarchitecture] to [public];
    go
     
    --创建扩展属性
    exec sys.sp_addextendedproperty @name=N'testcrituer', @value=N'测试创建数据库架构' , @level0type=N'schema',@level0name=N'testarchitecture'
    go

## 示例结果：使用T-SQL脚本创建数据库架构需要刷新数据库才能查看结果。 ##

![clipboard.png][clipboard.png 5]

[clipboard.png]: /images/20220302/53d026b50b594446b50f45ae9dd54070.png
[clipboard.png 1]: /images/20220302/a953d22872424829ac2b95b3992d1ee7.png
[clipboard.png 2]: /images/20220302/45e6437eacb643c98b02215b0247d1c1.png
[clipboard.png 3]: /images/20220302/0d6d6a7cf1c5457da9989cfd33e8514f.png
[clipboard.png 4]: /images/20220302/5b449541bec340d9acad15274801fd75.png
[clipboard.png 5]: /images/20220302/01462cbce8144e91b2808741def431d9.png