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

--為登陸賬戶創(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ù)庫角色，賦予數(shù)據(jù)庫用戶“db_owner”權(quán)限
exec sp_addrolemember 'db_owner', 'dba'

--讓 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

--創(chuàng)建數(shù)據(jù)庫mydb和mydb2

--在mydb和mydb2中創(chuàng)建測試表，默認(rèn)是dbo這個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ù)庫schema, user 的存儲過程
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ù)庫用戶（create user）,在mydb數(shù)據(jù)庫中的security中的user下可以找到新創(chuàng)建的dba
create user dba for login dba with default_schema=dbo

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

--讓 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

--禁用登陸帳戶
alter login dba disable
--啟用登陸帳戶
alter login dba enable

--登陸帳戶改名
alter login dba with name=dba_tom

--登陸帳戶改密碼： 
alter login dba with password='aabb@ccdd'

--數(shù)據(jù)庫用戶改名： 
alter user dba with name=dba_tom

--更改數(shù)據(jù)庫用戶 defult_schema： 
alter user dba with default_schema=sales

--刪除數(shù)據(jù)庫用戶： 
drop user dba

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

/*--示例說明
 示例在數(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實例后，它只具有表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)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對實體定義了 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)限已不再推薦使用，并且只保留用于兼容性目的。它并不表示對實體定義了 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.Employees


USE 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'

GRANT INSERT ON TableA TO RoleA
GO
EXEC sp_addrolemember RoleA, 'UserA' -- 用戶UserA將有TableA的INSERT權(quán)限
GO

REVOKE INSERT ON TableA FROM RoleA -- 用戶UserA將沒有TableA的INSERT權(quán)限，收回權(quán)限
GO

GRANT 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)限。