make-security-db.sql

来自「东软内部材料（四）asp等相关的教学案例 」· SQL 代码 · 共 41 行

/****** Object:  Table [dbo].[Users]    Script Date: 4/9/2001 5:02:38 PM ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Users]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Users]
GO

/****** Object:  Login SqlUser    Script Date: 4/9/2001 5:02:37 PM ******/
if not exists (select * from master.dbo.syslogins where loginname = N'SqlUser')
BEGIN
   declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'UserList', @loginlang = N'us_english'
   if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
      select @logindb = N'master'
   if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
      select @loginlang = @@language
   exec sp_addlogin N'SqlUser', null, @logindb, @loginlang
END
GO

/****** Object:  User SqlUser    Script Date: 4/9/2001 5:02:37 PM ******/
if not exists (select * from dbo.sysusers where name = N'SqlUser' and uid < 16382)
   EXEC sp_grantdbaccess N'SqlUser', N'SqlUser'
GO

/****** Object:  Table [dbo].[Users]    Script Date: 4/9/2001 5:02:39 PM ******/
CREATE TABLE [dbo].[Users] (
   [UserName] [varchar] (50) NOT NULL ,
   [Password] [varchar] (50) NOT NULL ,
   [BGColor] [varchar] (50) NULL
) ON [PRIMARY]
GO

GRANT  SELECT ,  INSERT ,  DELETE ,  UPDATE  ON [dbo].[Users]  TO [SqlUser]
GO

INSERT INTO users (UserName, Password) VALUES ('billjones', 'test')
INSERT INTO users (UserName, Password) VALUES ('marthasmith', 'test')
INSERT INTO users (UserName, Password) VALUES ('joesoap', 'test')
INSERT INTO users (UserName, Password, BGColor) VALUES ('sarahware', '87F8ED9157125FFC4DA9E06A7B8011AD80A53FE1', 'gainsboro')
INSERT INTO users (UserName, Password, BGColor) VALUES ('timtom', 'B12B629561D2CFFC1A73CE996F95D1F529F70BAC', 'lightgreen')
INSERT INTO users (UserName, Password, BGColor) VALUES ('billygoat', '9743367EF85DF9405A17FA9B3523524E8EC33B5E', 'yellow')
GO