removeschema.txt

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

/* RemoveSchema.txt */

USE LumigentDemoDB

/* The service table may have already been dropped during guided tour */
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE id = object_id(N'Service') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Service
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE id = object_id(N'Customers') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Customers
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE id = object_id(N'Employees') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE Employees

EXEC sp_droprolemember 'db_datareader', 'NewApp'
EXEC sp_droprolemember 'db_datawriter', 'NewApp'
EXEC sp_droprolemember 'db_datareader', 'Audit'
EXEC sp_droprolemember 'db_datawriter', 'Audit'
EXEC sp_droprolemember 'db_datareader', 'Sales'
EXEC sp_droprolemember 'db_datawriter', 'Sales'
EXEC sp_droprolemember 'db_datareader', 'PriceReview'
EXEC sp_droprolemember 'db_datawriter', 'PriceReview'
EXEC sp_droprolemember 'db_datareader', 'CustomerService'
EXEC sp_droprolemember 'db_datawriter', 'CustomerService'

IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE name = 'NewApp') EXEC sp_dropuser 'NewApp'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE name = 'Audit') EXEC sp_dropuser 'Audit'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE name = 'Sales') EXEC sp_dropuser 'Sales'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE name = 'PriceReview') EXEC sp_dropuser 'PriceReview'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE name = 'CustomerService') EXEC sp_dropuser 'CustomerService'
IF EXISTS (SELECT * FROM DBO.SYSUSERS WHERE name = 'Demo_Login') EXEC sp_dropuser 'Demo_Login'


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