SQL語句操作SQL SERVER資料庫登入名稱、使用者及許可權

標籤：prim   登入   預設   sqlserver   查詢   保留   add   sdn   account   

要想成功訪問 SQL Server 資料庫中的資料， 我們需要兩個方面的授權：

1. 獲得准許串連 SQL Server 服務器的權利；
2. 獲得訪問特定資料庫中資料的權利（select, update, delete, create table ...）。

假設，我們準備建立一個 dba 資料庫帳戶，用來管理資料庫 mydb。

1. 首先在 SQL Server 服務器層級，建立登陸帳戶（create login）

--建立登陸帳戶（create login）

create login dba with password=‘[email protected]‘, default_database=mydb

登陸帳戶名稱為：“dba”，登陸密碼：[email protected]”，預設串連到的資料庫：“mydb”。 這時候，dba 帳戶就可以串連到 SQL Server 服務器上了。但是此時還不能 訪問資料庫中的對象（嚴格的說，此時 dba 帳戶預設是 guest 資料庫使用者身份， 可以訪問 guest 能夠訪問的資料庫物件）。

要使 dba 帳戶能夠在 mydb 資料庫中訪問自己需要的對象， 需要在資料庫 mydb 中建立一個“資料庫使用者”，賦予這個“資料庫使用者” 某些存取權限，並且把登陸帳戶“dba” 和這個“資料庫使用者” 映射起來。 習慣上，“資料庫使用者” 的名字和 “登陸帳戶”的名字相同，即：“dba”。 建立“資料庫使用者”和建立映射關係只需要一步即可完成：

2. 建立資料庫使用者（create user）：

--為登陸賬戶建立資料庫使用者（create user）,在mydb資料庫中的security中的user下可以找到新建立的dba

create user dba for login dba with default_schema=dbo

並指定資料庫使用者“dba” 的預設 schema 是“dbo”。這意味著 使用者“dba” 在執行“select * from t”，實際上執行的是 “select * from dbo.t”。

3. 通過加入資料庫角色，賦予資料庫使用者“dba”許可權：

--通過加入資料庫角色，賦予資料庫使用者“db_owner”許可權

exec sp_addrolemember ‘db_owner‘, ‘dba‘

此時，dba 就可以全權管理資料庫 mydb 中的對象了。

如果想讓 SQL Server 登陸帳戶“dba”訪問多個資料庫，比如 mydb2。 可以讓 sa 執行下面的語句：

--讓 SQL Server 登陸帳戶“dba”訪問多個資料庫

use mydb2go create user dba for login dba with default_schema=dbogo exec sp_addrolemember ‘db_owner‘, ‘dba‘ go

此時，dba 就可以有兩個資料庫 mydb, mydb2 的系統管理權限了！

完整的程式碼範例

--建立資料庫mydb和mydb2

--在mydb和mydb2中建立測試表，預設是dbo這個schema

CREATE TABLE DEPT       (DEPTNO int primary key,        DNAME VARCHAR(14),        LOC VARCHAR(13) );--插入資料

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‘);--查看資料庫schema, user 的預存程序

select * from sys.database_principalsselect * from sys.schemas select * from sys.server_principals--建立登陸帳戶（create login）

create login dba with password=‘[email protected]‘, default_database=mydb--為登陸賬戶建立資料庫使用者（create user）,在mydb資料庫中的security中的user下可以找到新建立的dba

create user dba for login dba with default_schema=dbo--通過加入資料庫角色，賦予資料庫使用者“db_owner”許可權

exec sp_addrolemember ‘db_owner‘, ‘dba‘

--讓 SQL Server 登陸帳戶“dba”訪問多個資料庫

use mydb2go create user dba for login dba with default_schema=dbogo 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=‘[email protected]‘

--資料庫使用者改名： 

alter user dba with name=dba_tom--更改資料庫使用者 defult_schema： 

alter user dba with default_schema=sales--刪除資料庫使用者： 

drop user dba--刪除 SQL Server登陸帳戶： 

drop login dba

使用預存程序來完成使用者建立

下面一個執行個體來說明在sqlserver中如何使用預存程序建立角色，重建登入，以及如何為登入授權等問題。

 

[sql] view plain copy  print?

1. /*--樣本說明  
2.         樣本在資料庫InsideTSQL2008中建立一個擁有表HR.Employees的所有許可權、擁有表Sales.Orders的SELECT許可權的角色r_test  
3.     隨後建立了一個登入l_test，然後在資料庫InsideTSQL2008中為登入l_test建立了使用者賬戶u_test  
4.     同時將使用者賬戶u_test添加到角色r_test中，使其通過許可權繼承擷取了與角色r_test一樣的許可權  
5.     最後使用DENY語句拒絕了使用者賬戶u_test對錶HR.Employees的SELECT許可權。  
6.     經過這樣的處理，使用l_test登入SQL Server執行個體後，它只具有表Sales.Orders的select許可權和對錶HR.Employees出select外的所有許可權。  
7. --*/  
8.   
9.   
10. USE InsideTSQL2008  
11.   
12. --建立角色 r_test  
13. EXEC sp_addrole ‘r_test‘  
14.   
15. --添加登入 l_test,設定密碼為pwd,預設資料庫為pubs  
16. EXEC sp_addlogin ‘l_test‘,‘[email protected]‘,‘InsideTSQL2008‘  
17.   
18. --為登入 l_test 在資料庫 pubs 中添加安全賬戶 u_test  
19. EXEC sp_grantdbaccess ‘l_test‘,‘u_test‘  
20.   
21. --添加 u_test 為角色 r_test 的成員  
22. EXEC sp_addrolemember ‘r_test‘,‘u_test‘  
23.   
24.   
25. --用l_test登陸,發現在SSMS中找不到仍和表，因此執行下述兩條語句出錯。  
26. select * from Sales.Orders  
27. select * from HR.Employees  
28.   
29. --授予角色 r_test 對 HR.Employees 表的所有許可權  
30. GRANT ALL ON HR.Employees TO r_test  
31. --The ALL permission is deprecated and maintained only for compatibility.   
32. --It DOES NOT imply ALL permissions defined on the entity.  
33. --ALL 許可權已不再推薦使用，並且只保留用於相容性目的。它並不表示對實體定義了 ALL 許可權。  
34.   
35. --測試可以查詢表HR.Employees，但是Sales.Orders無法查詢  
36. select * from HR.Employees  
37.   
38.   
39. --如果要收回許可權，可以使用如下語句。（可選擇執行）  
40. revoke all on HR.Employees from r_test  
41. --ALL 許可權已不再推薦使用，並且只保留用於相容性目的。它並不表示對實體定義了 ALL 許可權。  
42.   
43.   
44. --授予角色 r_test 對 Sales.Orders 表的 SELECT 許可權  
45. GRANT SELECT ON Sales.Orders TO r_test  
46.   
47. --用l_test登陸,發現可以查詢Sales.Orders和HR.Employees兩張表  
48. select * from Sales.Orders  
49. select * from HR.Employees  
50.   
51. --拒絕安全賬戶 u_test 對 HR.Employees 表的 SELECT 許可權  
52. DENY SELECT ON HR.Employees TO u_test  
53.   
54. --再次執行查詢HR.Employees表的語句，提示：拒絕了對對象 ‘Employees‘ (資料庫 ‘InsideTSQL2008‘，架構 ‘HR‘)的 SELECT 許可權。  
55. select * from HR.Employees  
56.   
57. --重新授權  
58. GRANT SELECT ON HR.Employees TO u_test  
59.   
60. --重新查詢，可以查詢出結果。  
61. select * from HR.Employees  
62.   
63.   
64. USE InsideTSQL2008  
65. --從資料庫中刪除安全賬戶,failed  
66. EXEC sp_revokedbaccess ‘u_test‘  
67. --刪除角色 r_test,failed  
68. EXEC sp_droprole ‘r_test‘  
69. --刪除登入 l_test,success  
70. EXEC sp_droplogin ‘l_test‘  

 

revoke 與 deny的區別

revoke：收回之前被授與權限

deny：拒絕給當前資料庫內的安全帳戶授予許可權並防止安全帳戶通過其組或角色成員資格繼承許可權。比如UserA所在的角色群組有inset許可權，但是我們Deny UserA使其沒有insert許可權，那麼以後即使UserA再怎麼到其他含有Insert的角色群組中去，還是沒有insert許可權，除非該使用者被顯示授權。

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

執行個體 [sql] view plain copy  print?

1. GRANT INSERT ON TableA TO RoleA  
2. GO  
3. EXEC sp_addrolemember RoleA, ‘UserA‘ -- 使用者UserA將有TableA的INSERT許可權  
4. GO  
5.   
6. REVOKE INSERT ON TableA FROM RoleA -- 使用者UserA將沒有TableA的INSERT許可權，收回許可權  
7. GO  
8.   
9. GRANT INSERT ON TableA TORoleA --重新給RoleA以TableA的INSERT許可權  
10. GO   
11.   
12. DENY INSERT ON TableA TO UserA -- 雖然使用者UserA所在RoleA有TableA的INSERT許可權，但UserA本身被DENY了，所以使用者UserA將沒有TableA的INSERT許可權。  
13. 摘自：http://blog.csdn.net/a497785609/article/details/47686659

SQL語句操作SQL SERVER資料庫登入名稱、使用者及許可權