積沙成塔

create user u1 without login
create user u2 without login

go

--建立資料庫角色
create role u1role authorization dbo
create role u2role authorization dbo

go

--建立資料庫使用者所對要對應的結構描述
create schema u1 authorization dbo

go

create schema u2 authorization dbo

go

--授權資料庫角色相對應的結構描述權限
grant alter,delete,execute,insert,references,select,update,view definition 
on schema::u1 to u1role

grant alter,delete,execute,insert,references,select,update,view definition 
on schema::u2 to u2role

go

--授權資料庫角色建立資料庫物件的權限
grant create table,create procedure,create function,create view to u1role

grant create table,create procedure,create function,create view to u2role

go

--將使用者加入所對應的資料庫角色
exec sp_addrolemember 'u1role', 'u1'
exec sp_addrolemember 'u2role', 'u2'

go

--授權資料庫使用者讀取資料的權限
exec sp_addrolemember 'db_datareader', 'u1'
exec sp_addrolemember 'db_datareader', 'u2'

go

--使用者u1在結構描述u1建立資料表及新增資料
execute as user = 'u1'
select USER_NAME()
create table u1.tab1(c1 int)
go

insert into u1.tab1 values (1),(2)
go

select *
from u1.tab1
go

revert 


--使用者u2在結構描述u2建立資料表及新增資料
execute as user = 'u2'
select USER_NAME()

create table u2.tab1(c1 int)
go

insert into u2.tab1 values (1),(2)

select *
from u2.tab1

create table u2.tab2(c1 int)
go

insert into u2.tab2 values (3),(4)
go

revert 


--測試使用u1查詢u2所建立的物件以及變更u2的資料及物件
execute as user = 'u1'

--成功
select *
from u2.tab1

select *
from u2.tab2

--失敗
insert into u2.tab1 values (3)
drop table u2.tab1

revert


--刪除相關測試用的資料庫物件
drop table u1.tab1
drop table u2.tab1
drop table u2.tab2
drop schema u1
drop schema u2
drop user u1
drop user u2
drop role u1role
drop role u2role