一、數(shù)據(jù)庫(kù)的組成

　　在SQL Server 2008中，用戶如何訪問(wèn)及使用數(shù)據(jù)庫(kù)，就需要正確了解數(shù)據(jù)庫(kù)中所有對(duì)象及其設(shè)置。數(shù)據(jù)庫(kù)就像一個(gè)容器，它里面除了存放著數(shù)據(jù)的表之外，還有視圖、存儲(chǔ)過(guò)程、觸發(fā)器、約束等數(shù)據(jù)庫(kù)對(duì)象。數(shù)據(jù)庫(kù)管理的核心任務(wù)包括創(chuàng)建、操作和維護(hù)數(shù)據(jù)庫(kù)。

二、數(shù)據(jù)庫(kù)文件及其文件組

　　數(shù)據(jù)庫(kù)的存儲(chǔ)結(jié)構(gòu)分為邏輯存儲(chǔ)結(jié)構(gòu)和物理存儲(chǔ)結(jié)構(gòu)。

• 邏輯存儲(chǔ)結(jié)構(gòu)：說(shuō)明數(shù)據(jù)庫(kù)是由哪些性質(zhì)的信息所組成。SQL Server的數(shù)據(jù)庫(kù)不僅僅只是數(shù)據(jù)的存儲(chǔ)，所有與數(shù)據(jù)處理操作相關(guān)的信息都存儲(chǔ)在數(shù)據(jù)庫(kù)中。
• 物理存儲(chǔ)結(jié)構(gòu)：數(shù)據(jù)庫(kù)在磁盤上是以文件為單位存儲(chǔ)的，由數(shù)據(jù)庫(kù)文件和事務(wù)日志文件組成，一個(gè)數(shù)據(jù)庫(kù)至少應(yīng)該包含一個(gè)數(shù)據(jù)庫(kù)文件和一個(gè)事務(wù)日志文件。

　　SQL Server數(shù)據(jù)庫(kù)系統(tǒng)中的數(shù)據(jù)庫(kù)文件是由數(shù)據(jù)文件和日志文件組成的，數(shù)據(jù)文件以盤區(qū)為單位存儲(chǔ)在存儲(chǔ)器中。

2.1、數(shù)據(jù)文件

　　數(shù)據(jù)庫(kù)文件是指數(shù)據(jù)庫(kù)中用來(lái)存放數(shù)據(jù)庫(kù)數(shù)據(jù)和數(shù)據(jù)庫(kù)對(duì)象的文件，一個(gè)數(shù)據(jù)庫(kù)可以有一個(gè)或多個(gè)數(shù)據(jù)庫(kù)文件，一個(gè)數(shù)據(jù)庫(kù)文件只能屬于一個(gè)數(shù)據(jù)庫(kù)。當(dāng)有多個(gè)數(shù)據(jù)庫(kù)文件時(shí)，有一個(gè)文件被定為主數(shù)據(jù)文件，用來(lái)存儲(chǔ)數(shù)據(jù)庫(kù)的啟動(dòng)信息和部分或全部數(shù)據(jù)，一個(gè)數(shù)據(jù)庫(kù)只能有一個(gè)主數(shù)據(jù)庫(kù)文件。數(shù)據(jù)文件則劃分為不同的頁(yè)面和區(qū)域，頁(yè)是SQL Server存儲(chǔ)數(shù)據(jù)的基本單位。

• 主數(shù)據(jù)文件：是數(shù)據(jù)庫(kù)的起點(diǎn)，指向數(shù)據(jù)庫(kù)文件的其他部分，每個(gè)數(shù)據(jù)庫(kù)都有一個(gè)主要數(shù)據(jù)文件，擴(kuò)展名為.mdf。
• 次數(shù)據(jù)文件：包含除主數(shù)據(jù)庫(kù)文件之外的所有數(shù)據(jù)文件，一個(gè)數(shù)據(jù)庫(kù)可以沒(méi)有次數(shù)據(jù)文件，也可以有多個(gè)次數(shù)據(jù)文件，擴(kuò)展名為.ndf。
• 事務(wù)日志文件：包含恢復(fù)數(shù)據(jù)庫(kù)所有事務(wù)日志的信息。每個(gè)數(shù)據(jù)庫(kù)必須至少有一個(gè)事務(wù)日志文件，當(dāng)然也可以有多個(gè)，事務(wù)日志文件的推薦文件擴(kuò)展名是.ldf
• 文件流( Filestream)：可以使得基于 SQLServer的應(yīng)用程序能在文件系統(tǒng)中存儲(chǔ)非結(jié)構(gòu)化的數(shù)據(jù)，如文檔、圖片、音頻等，文件流主要將SQLServer數(shù)據(jù)庫(kù)引擎和新技術(shù)文件系統(tǒng)(NTFS)集成在一起，它主要以varbinary (max)數(shù)據(jù)類型存儲(chǔ)數(shù)據(jù)。

2.2、日志文件

　　SQL Server的日志是由一系列日志記錄組成，日志文件中記錄了存儲(chǔ)數(shù)據(jù)庫(kù)的更新情況等事務(wù)日志信息，用戶對(duì)數(shù)據(jù)庫(kù)進(jìn)行的插入、刪除和更新等操作都會(huì)記錄在日志文件中。當(dāng)數(shù)據(jù)庫(kù)損壞時(shí)，可以根據(jù)日志文件來(lái)分析出錯(cuò)的原因，或者數(shù)據(jù)丟失時(shí)，還可以使用事務(wù)日志恢復(fù)數(shù)據(jù)庫(kù)。每一個(gè)數(shù)據(jù)庫(kù)至少必須擁有一個(gè)事務(wù)日志文件，而且允許擁有多個(gè)日志文件。
　　SQL Server2012不強(qiáng)制使用.mdf、.ndf或者.ldf作為文件的擴(kuò)展名，但建議使用這些擴(kuò)展名幫助標(biāo)準(zhǔn)文件的用途。數(shù)據(jù)庫(kù)中的所有文件的位置都記錄在master數(shù)據(jù)庫(kù)和該數(shù)據(jù)庫(kù)在主數(shù)據(jù)文件中。

三、數(shù)據(jù)庫(kù)的分類

3.1、系統(tǒng)數(shù)據(jù)庫(kù)：由系統(tǒng)服務(wù)提供

　　SQLServer系統(tǒng)數(shù)據(jù)庫(kù)分別是：master、model、tempdb、msdb數(shù)據(jù)庫(kù)。

　　3.1.1、master數(shù)據(jù)庫(kù)

　　master是SQL Server中最重要的數(shù)據(jù)庫(kù)，是整個(gè)數(shù)據(jù)庫(kù)服務(wù)器的核心。用戶不能直接修改master數(shù)據(jù)庫(kù)，如果master數(shù)據(jù)庫(kù)損壞了，那么整個(gè)SQL Server服務(wù)器將不能工作。
　　master數(shù)據(jù)庫(kù)中包含了以下的一些內(nèi)容：

• 所有用戶的登錄信息
• 用戶所在的組
• 所有系統(tǒng)的配置選項(xiàng)
• 服務(wù)器中本地?cái)?shù)據(jù)庫(kù)的名稱和信息
• SQL Server的初始化方式等

　　master數(shù)據(jù)庫(kù)保存系統(tǒng)表用于系統(tǒng)的總體控制。如在新建一個(gè)數(shù)據(jù)庫(kù)時(shí)，在master數(shù)據(jù)庫(kù)的sysdatabases表中插入對(duì)應(yīng)的記錄。
　　作為一個(gè)數(shù)據(jù)庫(kù)管理員，應(yīng)該定期備份master數(shù)據(jù)庫(kù)。

　　3.1.2、model數(shù)據(jù)庫(kù)

　　model數(shù)據(jù)庫(kù)是SQL Server中創(chuàng)建數(shù)據(jù)庫(kù)的模板，如果用戶

• 希望創(chuàng)建的數(shù)據(jù)庫(kù)有相同的初始化文件大小，則可以在model數(shù)據(jù)庫(kù)中保存文件大小的信息；
• 希望所有的數(shù)據(jù)庫(kù)中都有相同的一個(gè)或多個(gè)表，可以將該數(shù)據(jù)表保存在model數(shù)據(jù)庫(kù)中。

　　新創(chuàng)建的數(shù)據(jù)庫(kù)以model數(shù)據(jù)庫(kù)中的數(shù)據(jù)作為模板，因此在修改model數(shù)據(jù)庫(kù)之前要考慮到，任何對(duì)model數(shù)據(jù)庫(kù)中數(shù)據(jù)的修改都要影響到所有使用該模板創(chuàng)建的數(shù)據(jù)庫(kù)。
　　由于model數(shù)據(jù)庫(kù)作為其他任意數(shù)據(jù)庫(kù)的模板，系統(tǒng)中必須保留，不能刪除。
　　在更改model數(shù)據(jù)庫(kù)時(shí)需要注意：任意新建的數(shù)據(jù)庫(kù)至少要比model數(shù)據(jù)庫(kù)大。如果將model數(shù)據(jù)庫(kù)大小更改為100MB，則不能創(chuàng)建小于100MB的數(shù)據(jù)庫(kù)。

　　3.1.3、tempdb數(shù)據(jù)庫(kù)

　　msdb提供了運(yùn)行SQL Server Agent工作的信息。SQL Server Agent是SQL Server中的一個(gè)Windows服務(wù)，該服務(wù)用來(lái)運(yùn)行制定的計(jì)劃任務(wù)。如在計(jì)劃對(duì)一個(gè)數(shù)據(jù)庫(kù)每夜進(jìn)行備份時(shí)，則在msdb數(shù)據(jù)庫(kù)中有一個(gè)相應(yīng)的記錄項(xiàng)。

　　3.1.4、msdb數(shù)據(jù)庫(kù)

　　tempdb是SQL Server中的一個(gè)臨時(shí)數(shù)據(jù)庫(kù)，用于存放臨時(shí)對(duì)象或中間結(jié)果，只要?jiǎng)?chuàng)建臨時(shí)表，則臨時(shí)表會(huì)創(chuàng)建在tempdb數(shù)據(jù)庫(kù)中。SQL Server關(guān)閉后，該數(shù)據(jù)庫(kù)中的內(nèi)容被清空，每次重新啟動(dòng)服務(wù)器之后，tempdb數(shù)據(jù)庫(kù)將被完全重建。

　　Tempdb數(shù)據(jù)庫(kù)保存系統(tǒng)運(yùn)行過(guò)程中產(chǎn)生的臨時(shí)表和存儲(chǔ)過(guò)程。當(dāng)然，它還滿足其他的臨時(shí)存儲(chǔ)要求，比如保存SQL Server生成的存儲(chǔ)表等。Tempdb數(shù)據(jù)庫(kù)是一個(gè)全局咨詢，任何連接到系統(tǒng)的用戶都可以在該數(shù)據(jù)中產(chǎn)生臨時(shí)表和存儲(chǔ)過(guò)程。Tempdb數(shù)據(jù)庫(kù)在每次SQL Server啟動(dòng)的時(shí)候，都會(huì)清空該數(shù)據(jù)庫(kù)中的內(nèi)容，所以每次啟動(dòng)SQL Server后，該表都是干凈的。臨時(shí)表和存儲(chǔ)過(guò)程在連接斷開(kāi)后會(huì)自動(dòng)除去，而且當(dāng)系統(tǒng)關(guān)閉后不會(huì)有任何活動(dòng)連接，因此，Tempdb數(shù)據(jù)庫(kù)中沒(méi)有任何內(nèi)容會(huì)從SQL Server的一個(gè)會(huì)話保存到另一個(gè)會(huì)話中。
　　默認(rèn)情況下，在SQL Server在運(yùn)行時(shí)Tempdb數(shù)據(jù)庫(kù)會(huì)根據(jù)需要自動(dòng)增長(zhǎng)。不過(guò)，與其它數(shù)據(jù)庫(kù)不同，每次啟動(dòng)數(shù)據(jù)庫(kù)引擎時(shí)，它會(huì)重置為其初始大小。如果為Tempdb數(shù)據(jù)庫(kù)定義的大小較小，則每次重新啟動(dòng)SQL Server時(shí)，將Tempdb數(shù)據(jù)庫(kù)的大小自動(dòng)增加到支持工作負(fù)荷所需的大小這一工作可能會(huì)成為系統(tǒng)處理負(fù)荷的一部分。為避免這種開(kāi)銷，可已使用ALTER DATABASE 增加Tempdb數(shù)據(jù)庫(kù)的大小。

3.2、用戶數(shù)據(jù)庫(kù)：用戶或程序員創(chuàng)建的數(shù)據(jù)庫(kù)

四、創(chuàng)建數(shù)據(jù)庫(kù)用戶及授權(quán)

• 使用SSMS圖形界面創(chuàng)建數(shù)據(jù)庫(kù)

?　　①、打開(kāi)安全性--登錄名，右鍵 新建用戶

　　

　　②、此時(shí)會(huì)出現(xiàn)Windows和SQL server身份驗(yàn)證

　　③、此時(shí)先選擇Windows身份登錄，點(diǎn)擊 搜索，則彈出 選擇用戶和組的彈出框，在其中輸入Windows系統(tǒng)中的用戶

　　

?

　　④、打開(kāi) 控制面板--選擇用戶賬戶--管理其他賬戶---選擇一個(gè)新賬戶，此時(shí)選擇fww用戶

　　

　　⑤、在輸入框中寫入fww，點(diǎn)擊 檢查名稱，則自動(dòng)加入了全名

?　　⑥、則在登錄名中自動(dòng)代入了Windows用戶，點(diǎn)擊確定，則用戶添加成功。

　　

　　⑦、斷開(kāi)此時(shí)的連接

　　⑧、新建fww對(duì)應(yīng)的連接，但是此時(shí)賬戶默認(rèn)為Administrator，且為灰色，無(wú)法更改

　　

　　⑨、按Alt+ Ctrl+Delete鍵，切換用戶

　　

　　⑩、則Sqlserver的登錄賬戶則變?yōu)锽almyLee

　　

　　?、輸入賬戶信息，則用戶登錄成功。

• 使用Transact-SQL 語(yǔ)句創(chuàng)建用戶

　　①、首先在 SQL Server 服務(wù)器級(jí)別，創(chuàng)建登陸帳戶（create login）

　　語(yǔ)法：

CREATE LOGIN 用戶名 PASSWORD='密碼' DEFAULT_DATABASE=數(shù)據(jù)庫(kù);

　　示例：

--創(chuàng)建登陸帳戶（create login） create login dba with password='abcd1234@', default_database=LibraryDB;

　　②、創(chuàng)建數(shù)據(jù)庫(kù)用戶（create user）

　　語(yǔ)法：

CREATE USER 用戶名 FOR LOGIN 登錄用戶名 WITH DEFAULT_SCHEMA=數(shù)據(jù)庫(kù);

　　示例：

--為登陸賬戶創(chuàng)建數(shù)據(jù)庫(kù)用戶（create user）,在mydb數(shù)據(jù)庫(kù)中的security中的user下可以找到新創(chuàng)建的dba create user dba for login dba with default_schema=LibraryDB

　　并指定數(shù)據(jù)庫(kù)用戶“dba” 的默認(rèn) schema 是“LibraryDB”。這意味著 用戶“LibraryDB” 在執(zhí)行“select * from t”，實(shí)際上執(zhí)行的是 “select * from LibraryDB.t”。

③、通過(guò)加入數(shù)據(jù)庫(kù)角色，賦予數(shù)據(jù)庫(kù)用戶“dba”權(quán)限

語(yǔ)法：

?

格式：

--通過(guò)加入數(shù)據(jù)庫(kù)角色，賦予數(shù)據(jù)庫(kù)用戶“db_owner”權(quán)限 exec sp_addrolemember 'db_owner', 'dba'

此時(shí)，dba 就可以全權(quán)管理數(shù)據(jù)庫(kù) mydb 中的對(duì)象了。
如果想讓 SQL Server 登陸帳戶“dba”訪問(wèn)多個(gè)數(shù)據(jù)庫(kù)，比如 mydb2。 可以讓 sa 執(zhí)行下面的語(yǔ)句：

--讓 SQL Server 登陸帳戶“dba”訪問(wèn)多個(gè)數(shù)據(jù)庫(kù) use mydb2 go create user dba for login dba with default_schema=dbo go exec sp_addrolemember 'db_owner', 'dba' go

　　此時(shí)，dba 就可以有兩個(gè)數(shù)據(jù)庫(kù) mydb, mydb2 的管理權(quán)限了！

　　④、完整的代碼示例

--創(chuàng)建數(shù)據(jù)庫(kù)mydb和mydb2--在mydb和mydb2中創(chuàng)建測(cè)試表，默認(rèn)是dbo這個(gè)schema CREATE TABLE DEPT(DEPTNO int primary key,DNAME VARCHAR(14),LOC VARCHAR(13) );--插入數(shù)據(jù) INSERT INTO DEPT VALUES (101, 'ACCOUNTING', 'NEW YORK'); INSERT INTO DEPT VALUES (201, 'RESEARCH', 'DALLAS'); INSERT INTO DEPT VALUES (301, 'SALES', 'CHICAGO'); INSERT INTO DEPT VALUES (401, 'OPERATIONS', 'BOSTON');--查看數(shù)據(jù)庫(kù)schema, user 的存儲(chǔ)過(guò)程 select * from sys.database_principals select * from sys.schemas select * from sys.server_principals--創(chuàng)建登陸帳戶（create login） create login dba with password='abcd1234@', default_database=mydb--為登陸賬戶創(chuàng)建數(shù)據(jù)庫(kù)用戶（create user）,在mydb數(shù)據(jù)庫(kù)中的security中的user下可以找到新創(chuàng)建的dba create user dba for login dba with default_schema=dbo--通過(guò)加入數(shù)據(jù)庫(kù)角色，賦予數(shù)據(jù)庫(kù)用戶“db_owner”權(quán)限 exec sp_addrolemember 'db_owner', 'dba'--讓 SQL Server 登陸帳戶“dba”訪問(wèn)多個(gè)數(shù)據(jù)庫(kù) use mydb2 go create user dba for login dba with default_schema=dbo go exec sp_addrolemember 'db_owner', 'dba' go--禁用登陸帳戶 alter login dba disable --啟用登陸帳戶 alter login dba enable--登陸帳戶改名 alter login dba with name=dba_tom--登陸帳戶改密碼： alter login dba with password='aabb@ccdd'--數(shù)據(jù)庫(kù)用戶改名： alter user dba with name=dba_tom--更改數(shù)據(jù)庫(kù)用戶 defult_schema： alter user dba with default_schema=sales--刪除數(shù)據(jù)庫(kù)用戶： drop user dba--刪除 SQL Server登陸帳戶： drop login dba

• 使用存儲(chǔ)過(guò)程創(chuàng)建用戶

　　下面一個(gè)實(shí)例來(lái)說(shuō)明在sqlserver中如何使用存儲(chǔ)過(guò)程創(chuàng)建角色，重建登錄，以及如何為登錄授權(quán)等

/*--示例說(shuō)明示例在數(shù)據(jù)庫(kù)InsideTSQL2008中創(chuàng)建一個(gè)擁有表HR.Employees的所有權(quán)限、擁有表Sales.Orders的SELECT權(quán)限的角色r_test隨后創(chuàng)建了一個(gè)登錄l_test，然后在數(shù)據(jù)庫(kù)InsideTSQL2008中為登錄l_test創(chuàng)建了用戶賬戶u_test同時(shí)將用戶賬戶u_test添加到角色r_test中，使其通過(guò)權(quán)限繼承獲取了與角色r_test一樣的權(quán)限最后使用DENY語(yǔ)句拒絕了用戶賬戶u_test對(duì)表HR.Employees的SELECT權(quán)限。經(jīng)過(guò)這樣的處理，使用l_test登錄SQL Server實(shí)例后，它只具有表Sales.Orders的select權(quán)限和對(duì)表HR.Employees出select外的所有權(quán)限。 --*/USE InsideTSQL2008--創(chuàng)建角色 r_test EXEC sp_addrole 'r_test'--添加登錄 l_test,設(shè)置密碼為pwd,默認(rèn)數(shù)據(jù)庫(kù)為pubs EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'--為登錄 l_test 在數(shù)據(jù)庫(kù) pubs 中添加安全賬戶 u_test EXEC sp_grantdbaccess 'l_test','u_test'--添加 u_test 為角色 r_test 的成員 EXEC sp_addrolemember 'r_test','u_test'--用l_test登陸,發(fā)現(xiàn)在SSMS中找不到仍和表，因此執(zhí)行下述兩條語(yǔ)句出錯(cuò)。 select * from Sales.Orders select * from HR.Employees--授予角色 r_test 對(duì) HR.Employees 表的所有權(quán)限 GRANT ALL ON HR.Employees TO r_test --The ALL permission is deprecated and maintained only for compatibility. --It DOES NOT imply ALL permissions defined on the entity. --ALL 權(quán)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對(duì)實(shí)體定義了 ALL 權(quán)限。--測(cè)試可以查詢表HR.Employees，但是Sales.Orders無(wú)法查詢 select * from HR.Employees--如果要收回權(quán)限，可以使用如下語(yǔ)句。（可選擇執(zhí)行） revoke all on HR.Employees from r_test --ALL 權(quán)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對(duì)實(shí)體定義了 ALL 權(quán)限。--授予角色 r_test 對(duì) Sales.Orders 表的 SELECT 權(quán)限 GRANT SELECT ON Sales.Orders TO r_test--用l_test登陸,發(fā)現(xiàn)可以查詢Sales.Orders和HR.Employees兩張表 select * from Sales.Orders select * from HR.Employees--拒絕安全賬戶 u_test 對(duì) HR.Employees 表的 SELECT 權(quán)限 DENY SELECT ON HR.Employees TO u_test--再次執(zhí)行查詢HR.Employees表的語(yǔ)句，提示：拒絕了對(duì)對(duì)象 'Employees' (數(shù)據(jù)庫(kù) 'InsideTSQL2008'，架構(gòu) 'HR')的 SELECT 權(quán)限。 select * from HR.Employees--重新授權(quán) GRANT SELECT ON HR.Employees TO u_test--再次查詢，可以查詢出結(jié)果。 select * from HR.EmployeesUSE InsideTSQL2008 --從數(shù)據(jù)庫(kù)中刪除安全賬戶,failed EXEC sp_revokedbaccess 'u_test' --刪除角色 r_test,failed EXEC sp_droprole 'r_test' --刪除登錄 l_test,success EXEC sp_droplogin 'l_test'

revoke 與 deny的區(qū)別

revoke：收回之前被授予的權(quán)限
deny：拒絕給當(dāng)前數(shù)據(jù)庫(kù)內(nèi)的安全帳戶授予權(quán)限并防止安全帳戶通過(guò)其組或角色成員資格繼承權(quán)限。比如UserA所在的角色組有inset權(quán)限，但是我們Deny UserA使其沒(méi)有insert權(quán)限，那么以后即使UserA再怎么到其他含有Insert的角色組中去，還是沒(méi)有insert權(quán)限，除非該用戶被顯示授權(quán)。
簡(jiǎn)單來(lái)說(shuō)，deny就是將來(lái)都不許給，revoke就是收回已經(jīng)給予的。

示例：

GRANT INSERT ON TableA TO RoleA GO EXEC sp_addrolemember RoleA, 'UserA' -- 用戶UserA將有TableA的INSERT權(quán)限 GOREVOKE INSERT ON TableA FROM RoleA -- 用戶UserA將沒(méi)有TableA的INSERT權(quán)限，收回權(quán)限 GOGRANT INSERT ON TableA TORoleA --重新給RoleA以TableA的INSERT權(quán)限 GO DENY INSERT ON TableA TO UserA -- 雖然用戶UserA所在RoleA有TableA的INSERT權(quán)限，但UserA本身被DENY了，所以用戶UserA將沒(méi)有TableA的INSERT權(quán)限。

?

轉(zhuǎn)載于:https://www.cnblogs.com/BalmyLee/p/10945979.html

總結(jié)

以上是生活随笔為你收集整理的MSSQL → 02：数据库结构的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。