createschema.txt

来自「本人使用SQL SERVER 数据库3年」· 文本 代码 · 共 37 行

/* CreateSchema.txt */

USE master
IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name = N'NewApp') EXEC sp_addlogin N'NewApp'
IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name = N'Audit') EXEC sp_addlogin N'Audit'
IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name = N'Sales') EXEC sp_addlogin N'Sales'
IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name = N'PriceReview') EXEC sp_addlogin N'PriceReview'
IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name = N'CustomerService') EXEC sp_addlogin N'CustomerService'
IF NOT EXISTS (SELECT * FROM master..syslogins WHERE name = N'Demo_Login') EXEC sp_addlogin N'Demo_Login'


BACKUP DATABASE LumigentDemoDB TO LumigentDemoDB_db WITH INIT
use LumigentDemoDB
exec sp_adduser 'NewApp', 'NewApp'
exec sp_adduser 'Audit', 'Audit'
exec sp_adduser 'Sales', 'Sales'
exec sp_adduser 'PriceReview', 'PriceReview'
exec sp_adduser 'CustomerService', 'CustomerService'


exec sp_addrolemember 'db_datareader', 'NewApp'
exec sp_addrolemember 'db_datawriter', 'NewApp'
exec sp_addrolemember 'db_datareader', 'Audit'
exec sp_addrolemember 'db_datawriter', 'Audit'
exec sp_addrolemember 'db_datareader', 'Sales'
exec sp_addrolemember 'db_datawriter', 'Sales'
exec sp_addrolemember 'db_datareader', 'CustomerService'
exec sp_addrolemember 'db_datawriter', 'CustomerService'
exec sp_addrolemember 'db_datareader', 'PriceReview'
exec sp_addrolemember 'db_datawriter', 'PriceReview'

CREATE TABLE Employees (EmpID int NOT NULL , Last nvarchar (20) NOT NULL , First nvarchar (10) NOT NULL , PayRate money NULL)

CREATE TABLE Customers (CustID int NOT NULL , Last nvarchar (20) NOT NULL , First nvarchar (15) NOT NULL , Address nvarchar (60) NULL , City nvarchar (15) NULL , Region nvarchar (15) NULL , PostalCode nvarchar (10) NULL , Country nvarchar (15) NULL )

CREATE TABLE Service (ServID int NOT NULL , CustID int  NOT NULL , EmpID int NOT NULL , ServPrice money NOT NULL , ServName nvarchar (20) NOT NULL , ServDate datetime NULL)