正文
startTime datetime,
endTime datetime
)
CREATE TABLE [employees] (
empId int PRIMARY KEY,
name nvarchar(256),
databasePrincipalId int
)
CREATE TABLE [staffDuties] (
empId int,
wing int,
startTime datetime,
endTime datetime
)
CREATE TABLE [wings] (
wingId int PRIMARY KEY,
name nvarchar(128)
)
go
CREATE ROLE [nurse]
CREATE ROLE [doctor]
go
GRANT SELECT, UPDATE ON [patients] to [nurse]
GRANT SELECT, UPDATE ON [patients] to [doctor]
go
-- Create a user for each nurse & doctor (without logins to simplifydemo)
-- Add to corresponding role (in practice, these could also be WindowsGroups)
-- Add to employees table
CREATE USER [nurse_BartonC] WITHOUT LOGIN
ALTER ROLE [nurse] ADD MEMBER [nurse_BartonC]
INSERT INTO [employees] VALUES ( 1001, N'
张三丰
', DATABASE_PRINCIPAL_ID('nurse_BartonC'));
go
CREATE USER [nurse_AllenM] WITHOUT LOGIN
ALTER ROLE [nurse] ADD MEMBER [nurse_AllenM]
INSERT INTO [employees] VALUES ( 1002, N'
小静
', DATABASE_PRINCIPAL_ID('nurse_AllenM'));
go
CREATE USER [nurse_NightingaleF] WITHOUT LOGIN
ALTER ROLE [nurse] ADD MEMBER [nurse_NightingaleF]
INSERT INTO [employees] VALUES ( 1003, N'
小昭
', DATABASE_PRINCIPAL_ID('nurse_NightingaleF'));
go
CREATE USER [doctor_ApgarV] WITHOUT LOGIN
ALTER ROLE [doctor] ADD MEMBER [doctor_ApgarV]
INSERT INTO [employees] VALUES ( 2001, N'
张无忌
', DATABASE_PRINCIPAL_ID('doctor_ApgarV'));
go
CREATE USER [doctor_CharcotJ] WITHOUT LOGIN
