SQLServer之创建数据库架构
創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu)注意事項(xiàng)
包含 CREATE SCHEMA AUTHORIZATION 但未指定名稱的語(yǔ)句僅允許用于向后兼容性。 該語(yǔ)句未引起錯(cuò)誤，但未創(chuàng)建一個(gè)架構(gòu)。
CREATE SCHEMA 可以在單條語(yǔ)句中創(chuàng)建架構(gòu)以及該架構(gòu)所包含的表和視圖，并授予對(duì)任何安全對(duì)象的 GRANT、REVOKE 或 DENY 權(quán)限。 此語(yǔ)句必須作為一個(gè)單獨(dú)的批處理執(zhí)行。 CREATE SCHEMA 語(yǔ)句所創(chuàng)建的對(duì)象將在要?jiǎng)?chuàng)建的架構(gòu)內(nèi)進(jìn)行創(chuàng)建。
CREATE SCHEMA 事務(wù)是原子級(jí)的。 如果 CREATE SCHEMA 語(yǔ)句執(zhí)行期間出現(xiàn)任何錯(cuò)誤，則不會(huì)創(chuàng)建任何指定的安全對(duì)象，也不會(huì)授予任何權(quán)限。
由 CREATE SCHEMA 創(chuàng)建的安全對(duì)象可以任何順序列出，但引用其他視圖的視圖除外。 在這種情況下，被引用的視圖必須在引用它的視圖之前創(chuàng)建。
因此，GRANT 語(yǔ)句可以在創(chuàng)建某個(gè)對(duì)象自身之前對(duì)該對(duì)象授予權(quán)限，CREATE VIEW 語(yǔ)句也可以出現(xiàn)在創(chuàng)建該視圖所引用表的 CREATE TABLE 語(yǔ)句之前。 同樣，CREATE TABLE 語(yǔ)句可以在 CREATE SCHEMA 語(yǔ)句定義表之前聲明表的外鍵。
執(zhí)行 CREATE SCHEMA 的主體可以將另一個(gè)數(shù)據(jù)庫(kù)主體指定為要?jiǎng)?chuàng)建的架構(gòu)的所有者。 完成此操作需要另外的權(quán)限，如本主題下文中的“權(quán)限”部分所述。
新架構(gòu)由以下數(shù)據(jù)庫(kù)級(jí)別主體之一擁有：數(shù)據(jù)庫(kù)用戶、數(shù)據(jù)庫(kù)角色或應(yīng)用程序角色。 在架構(gòu)內(nèi)創(chuàng)建的對(duì)象由架構(gòu)所有者擁有，這些對(duì)象在 sys.objects 中的 principal_id為 NULL。 架構(gòu)所包含對(duì)象的所有權(quán)可轉(zhuǎn)讓給任何數(shù)據(jù)庫(kù)級(jí)主體，但架構(gòu)所有者始終保留對(duì)該架構(gòu)內(nèi)對(duì)象的 CONTROL 權(quán)限。
隱式架構(gòu)和用戶創(chuàng)建
在某些情況下，用戶可在沒(méi)有數(shù)據(jù)庫(kù)用戶帳戶（數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)主體）的情況下使用數(shù)據(jù)庫(kù)。 這可發(fā)生在以下情況中：
登錄名具有 CONTROL SERVER 特權(quán)。
Windows 用戶沒(méi)有單獨(dú)的數(shù)據(jù)庫(kù)用戶帳戶（數(shù)據(jù)庫(kù)中的數(shù)據(jù)庫(kù)主體），但以具有數(shù)據(jù)庫(kù)用戶帳戶（Windows 組的數(shù)據(jù)庫(kù)主體）的 Windows 組成員的身份訪問(wèn)數(shù)據(jù)庫(kù)。
如果沒(méi)有數(shù)據(jù)庫(kù)用戶帳戶的用戶在不指定現(xiàn)有架構(gòu)的情況下創(chuàng)建對(duì)象，則將在數(shù)據(jù)庫(kù)中自動(dòng)為該用戶創(chuàng)建數(shù)據(jù)庫(kù)主體和默認(rèn)架構(gòu)。 創(chuàng)建的數(shù)據(jù)庫(kù)主體和架構(gòu)采用的名稱將與連接到 SQL Server 時(shí)用戶使用的名稱（ SQL Server 身份驗(yàn)證登錄名或 Windows 用戶名）相同。
若要允許基于 Windows 組的用戶創(chuàng)建和擁有對(duì)象，此行為很有必要。 但這種行為可能將導(dǎo)致意外創(chuàng)建架構(gòu)和用戶。 為了避免隱式創(chuàng)建用戶和架構(gòu)，請(qǐng)盡可能顯式創(chuàng)建數(shù)據(jù)庫(kù)主體和分配默認(rèn)架構(gòu)。 或者，在數(shù)據(jù)庫(kù)中創(chuàng)建對(duì)象時(shí)，使用由兩部分或三部分組成的對(duì)象名稱顯式聲明現(xiàn)有架構(gòu)。
當(dāng)前支持不指定架構(gòu)名稱的 CREATE SCHEMA 語(yǔ)句，目的是為了向后兼容。 此類語(yǔ)句并不在數(shù)據(jù)庫(kù)中實(shí)際創(chuàng)建架構(gòu)，但它們會(huì)創(chuàng)建表和視圖，并授予權(quán)限。 主體不需要 CREATE SCHEMA 權(quán)限來(lái)執(zhí)行這一早期形式的 CREATE SCHEMA，因?yàn)椴粫?huì)創(chuàng)建任何架構(gòu)。 此功能將從 SQL Server 的未來(lái)版本中刪除。
需要對(duì)數(shù)據(jù)庫(kù)擁有 CREATE SCHEMA 權(quán)限。
若要?jiǎng)?chuàng)建在 CREATE SCHEMA 語(yǔ)句中指定的對(duì)象，用戶必須擁有相應(yīng)的 CREATE 權(quán)限。
若要指定其他用戶作為所創(chuàng)建架構(gòu)的所有者，則調(diào)用方必須具有對(duì)該用戶的 IMPERSONATE 權(quán)限。 如果指定一個(gè)數(shù)據(jù)庫(kù)角色作為所有者，則調(diào)用方必須擁有該角色的成員身份或?qū)υ摻巧珦碛?ALTER 權(quán)限。
使用SSMS數(shù)據(jù)庫(kù)管理工具創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu)
1、連接服務(wù)器-》展開數(shù)據(jù)庫(kù)文件夾-》選擇數(shù)據(jù)庫(kù)并展開-》展開安全性-》展開架構(gòu)-》右鍵單擊架構(gòu)文件夾選擇創(chuàng)建架構(gòu)。
2、在新建架構(gòu)彈出框-》點(diǎn)擊常規(guī)-》輸入新建架構(gòu)名稱-》點(diǎn)擊搜索選擇架構(gòu)所有者。
3、在新建架構(gòu)彈出框-》點(diǎn)擊權(quán)限-》點(diǎn)擊搜索選擇新建架構(gòu)的用戶或角色-》選擇用戶或角色后選擇新建架構(gòu)的權(quán)限。
4、在新建架構(gòu)彈出框-》點(diǎn)擊擴(kuò)展屬性-》輸入擴(kuò)展屬性名稱和值-》點(diǎn)擊確定。
5、不需要刷新即可在對(duì)象資源管理器中查看創(chuàng)建結(jié)果。
使用T-SQL腳本創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu)
語(yǔ)法
----聲明數(shù)據(jù)庫(kù)引用 --use database_name; --go----創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu) --create schema schema_name authorization owner_name --{ table_definition | view_definition | grant_statement | revoke_statement | deny_statement } --; --go語(yǔ)法解析
--語(yǔ)法解析
--database_name
--架構(gòu)所在的數(shù)據(jù)庫(kù)名
--schema_name
--在數(shù)據(jù)庫(kù)內(nèi)標(biāo)識(shí)架構(gòu)的名稱。
--authorization owner_name
--指定將擁有架構(gòu)的數(shù)據(jù)庫(kù)級(jí)主體的名稱。此主體還可以擁有其他架構(gòu)，并且可以不使用當(dāng)前架構(gòu)作為其默認(rèn)架構(gòu)。
--table_definition
--指定在架構(gòu)內(nèi)創(chuàng)建表的CREATE TABLE語(yǔ)句。執(zhí)行此語(yǔ)句的主體必須對(duì)當(dāng)前數(shù)據(jù)庫(kù)具有CREATE TABLE權(quán)限。
--view_definition
--指定在架構(gòu)內(nèi)創(chuàng)建視圖的CREATE VIEW語(yǔ)句。執(zhí)行此語(yǔ)句的主體必須對(duì)當(dāng)前數(shù)據(jù)庫(kù)具有CREATE VIEW權(quán)限。
--grant_statement
--指定可對(duì)除新架構(gòu)外的任何安全對(duì)象授予權(quán)限的GRANT語(yǔ)句。
--revoke_statement
--指定可對(duì)除新架構(gòu)外的任何安全對(duì)象撤消權(quán)限的REVOKE語(yǔ)句。
--deny_statement
--指定可對(duì)除新架構(gòu)外的任何安全對(duì)象拒絕授予權(quán)限的DENY語(yǔ)句。
示例
--聲明數(shù)據(jù)庫(kù)引用 use [testss]; goif exists(select * from sys.schemas where name='testarchitecture') --刪除數(shù)據(jù)庫(kù)架構(gòu)注釋 exec sys.sp_dropextendedproperty @name=N'testcrituer' , @level0type=N'schema',@level0name=N'testarchitecture';--刪除架構(gòu)下的所有表if exists(select * from sys.tables where name='schema_table1')drop table [testarchitecture].[schema_table1];go --刪除數(shù)據(jù)庫(kù)架構(gòu) drop schema testarchitecture; go--創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu) 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--授予創(chuàng)建序列 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--接管所有權(quán) 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--授予執(zhí)行 grant execute on schema::[testarchitecture] to [public]; go----授予并允許轉(zhuǎn)授插入 --grant insert on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授查看定義 --grant view definition on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授查看更改跟蹤 --grant view change tracking on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授創(chuàng)建序列 --grant create sequence on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授更改 --grant alter on schema::[testarchitecture] to [public] with grant option; --go-- --授予并允許轉(zhuǎn)授更新 --grant update on schema::[testarchitecture] to [public] with grant option; --go----接管并允許轉(zhuǎn)授所有權(quán) --grant take ownership on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授控制 --grant control on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授刪除 --grant delete on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授選擇 --grant select on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授引用 --grant references on schema::[testarchitecture] to [public] with grant option; --go----授予并允許轉(zhuǎn)授執(zhí)行 --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----拒絕創(chuàng)建序列 --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----拒絕所有權(quán) --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----拒絕執(zhí)行 --deny execute on schema::[testarchitecture] to [public]; --go--用戶或者角色 alter authorization on schema::[testarchitecture] to [public]; go--創(chuàng)建擴(kuò)展屬性 exec sys.sp_addextendedproperty @name=N'testcrituer', @value=N'測(cè)試創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu)' , @level0type=N'schema',@level0name=N'testarchitecture' go示例結(jié)果：使用T-SQL腳本創(chuàng)建數(shù)據(jù)庫(kù)架構(gòu)需要刷新數(shù)據(jù)庫(kù)才能查看結(jié)果。
總結(jié)
以上是生活随笔為你收集整理的SQLServer之创建数据库架构的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。
- 上一篇： 愿将一生献宏谋——送别于敏侧记
- 下一篇： 在创业之路上不断创新