參考文獻(xiàn)

http://database.51cto.com/art/201009/224075.htm

正文

要想成功訪問 SQL Server 數(shù)據(jù)庫中的數(shù)據(jù)， 我們需要兩個方面的授權(quán)：

獲得準(zhǔn)許連接 SQL Server 服務(wù)器的權(quán)利；

獲得訪問特定數(shù)據(jù)庫中數(shù)據(jù)的權(quán)利（select, update, delete, create table ...）。

假設(shè)，我們準(zhǔn)備建立一個 dba 數(shù)據(jù)庫帳戶，用來管理數(shù)據(jù)庫 mydb。

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

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

登陸帳戶名為：“dba”，登陸密碼：abcd1234@”，默認(rèn)連接到的數(shù)據(jù)庫：“mydb”。 這時候，dba 帳戶就可以連接到 SQL Server 服務(wù)器上了。但是此時還不能 訪問數(shù)據(jù)庫中的對象（嚴(yán)格的說，此時 dba 帳戶默認(rèn)是 guest 數(shù)據(jù)庫用戶身份， 可以訪問 guest 能夠訪問的數(shù)據(jù)庫對象）。

要使 dba 帳戶能夠在 mydb 數(shù)據(jù)庫中訪問自己需要的對象， 需要在數(shù)據(jù)庫 mydb 中建立一個“數(shù)據(jù)庫用戶”，賦予這個“數(shù)據(jù)庫用戶” 某些訪問權(quán)限，并且把登陸帳戶“dba” 和這個“數(shù)據(jù)庫用戶” 映射起來。 習(xí)慣上，“數(shù)據(jù)庫用戶” 的名字和 “登陸帳戶”的名字相同，即：“dba”。 創(chuàng)建“數(shù)據(jù)庫用戶”和建立映射關(guān)系只需要一步即可完成：

2. 創(chuàng)建數(shù)據(jù)庫用戶（create user）：

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

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

3. 通過加入數(shù)據(jù)庫角色，賦予數(shù)據(jù)庫用戶“dba”權(quán)限：

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

此時，dba 就可以全權(quán)管理數(shù)據(jù)庫 mydb 中的對象了。

如果想讓 SQL Server 登陸帳戶“dba”訪問多個數(shù)據(jù)庫，比如 mydb2。 可以讓 sa 執(zhí)行下面的語句：

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

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

完整的代碼示例

View Code

使用存儲過程來完成用戶創(chuàng)建

下面一個實(shí)例來說明在sqlserver中如何使用存儲過程創(chuàng)建角色，重建登錄，以及如何為登錄授權(quán)等問題。

/*--示例說明示例在數(shù)據(jù)庫InsideTSQL2008中創(chuàng)建一個擁有表HR.Employees的所有權(quán)限、擁有表Sales.Orders的SELECT權(quán)限的角色r_test隨后創(chuàng)建了一個登錄l_test，然后在數(shù)據(jù)庫InsideTSQL2008中為登錄l_test創(chuàng)建了用戶賬戶u_test同時將用戶賬戶u_test添加到角色r_test中，使其通過權(quán)限繼承獲取了與角色r_test一樣的權(quán)限最后使用DENY語句拒絕了用戶賬戶u_test對表HR.Employees的SELECT權(quán)限。經(jīng)過這樣的處理，使用l_test登錄SQL Server實(shí)例后，它只具有表Sales.Orders的select權(quán)限和對表HR.Employees出select外的所有權(quán)限。 --*/USE InsideTSQL2008--創(chuàng)建角色 r_test EXEC sp_addrole 'r_test'--添加登錄 l_test,設(shè)置密碼為pwd,默認(rèn)數(shù)據(jù)庫為pubs EXEC sp_addlogin 'l_test','a@cd123','InsideTSQL2008'--為登錄 l_test 在數(shù)據(jù)庫 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í)行下述兩條語句出錯。 select * from Sales.Orders select * from HR.Employees--授予角色 r_test 對 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)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對實(shí)體定義了 ALL 權(quán)限。--測試可以查詢表HR.Employees，但是Sales.Orders無法查詢 select * from HR.Employees--如果要收回權(quán)限，可以使用如下語句。（可選擇執(zhí)行） revoke all on HR.Employees from r_test --ALL 權(quán)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對實(shí)體定義了 ALL 權(quán)限。--授予角色 r_test 對 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 對 HR.Employees 表的 SELECT 權(quán)限 DENY SELECT ON HR.Employees TO u_test--再次執(zhí)行查詢HR.Employees表的語句，提示：拒絕了對對象 'Employees' (數(shù)據(jù)庫 '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ù)庫中刪除安全賬戶,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ù)庫內(nèi)的安全帳戶授予權(quán)限并防止安全帳戶通過其組或角色成員資格繼承權(quán)限。比如UserA所在的角色組有inset權(quán)限，但是我們Deny UserA使其沒有insert權(quán)限，那么以后即使UserA再怎么到其他含有Insert的角色組中去，還是沒有insert權(quán)限，除非該用戶被顯示授權(quán)。

簡單來說，deny就是將來都不許給，revoke就是收回已經(jīng)給予的。

實(shí)例

GRANT INSERT ON TableA TO RoleA GO EXEC sp_addrolemember RoleA, 'UserA' -- 用戶UserA將有TableA的INSERT權(quán)限 GOREVOKE INSERT ON TableA FROM RoleA -- 用戶UserA將沒有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將沒有TableA的INSERT權(quán)限。

?

總結(jié)

以上是生活随笔為你收集整理的在SQL Server中创建用户角色及授权的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

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