Visual Application Launcher

��USE [master]

GO

/****** Object:  Database [VAL]    Script Date: 03/17/2011 13:34:23 ******/

CREATE DATABASE [VAL] ON  PRIMARY 

( NAME = N'VAL_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VAL.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

 LOG ON 

( NAME = N'VAL_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\VAL_1.ldf' , SIZE = 1024KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)

GO

EXEC dbo.sp_dbcmptlevel @dbname=N'VAL', @new_cmptlevel=80

GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [VAL].[dbo].[sp_fulltext_database] @action = 'disable'
end

GO

ALTER DATABASE [VAL] SET ANSI_NULL_DEFAULT OFF 

GO

ALTER DATABASE [VAL] SET ANSI_NULLS OFF 

GO

ALTER DATABASE [VAL] SET ANSI_PADDING OFF 

GO

ALTER DATABASE [VAL] SET ANSI_WARNINGS OFF 

GO

ALTER DATABASE [VAL] SET ARITHABORT OFF 

GO

ALTER DATABASE [VAL] SET AUTO_CLOSE OFF 

GO

ALTER DATABASE [VAL] SET AUTO_CREATE_STATISTICS ON 

GO

ALTER DATABASE [VAL] SET AUTO_SHRINK OFF 

GO

ALTER DATABASE [VAL] SET AUTO_UPDATE_STATISTICS ON 

GO

ALTER DATABASE [VAL] SET CURSOR_CLOSE_ON_COMMIT OFF 

GO

ALTER DATABASE [VAL] SET CURSOR_DEFAULT  GLOBAL 

GO

ALTER DATABASE [VAL] SET CONCAT_NULL_YIELDS_NULL OFF 

GO

ALTER DATABASE [VAL] SET NUMERIC_ROUNDABORT OFF 

GO

ALTER DATABASE [VAL] SET QUOTED_IDENTIFIER OFF 

GO

ALTER DATABASE [VAL] SET RECURSIVE_TRIGGERS OFF 

GO

ALTER DATABASE [VAL] SET  DISABLE_BROKER 

GO

ALTER DATABASE [VAL] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

GO

ALTER DATABASE [VAL] SET DATE_CORRELATION_OPTIMIZATION OFF 

GO

ALTER DATABASE [VAL] SET TRUSTWORTHY OFF 

GO

ALTER DATABASE [VAL] SET ALLOW_SNAPSHOT_ISOLATION OFF 

GO

ALTER DATABASE [VAL] SET PARAMETERIZATION SIMPLE 

GO

ALTER DATABASE [VAL] SET  READ_WRITE 

GO

ALTER DATABASE [VAL] SET RECOVERY SIMPLE 

GO

ALTER DATABASE [VAL] SET  MULTI_USER 

GO

ALTER DATABASE [VAL] SET PAGE_VERIFY TORN_PAGE_DETECTION  

GO

ALTER DATABASE [VAL] SET DB_CHAINING OFF 





USE [VAL]

CREATE LOGIN [VALUser] WITH PASSWORD=N'ChangeThisPassword', DEFAULT_DATABASE=[VAL], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [VALUser] DISABLE

GO



GO

/****** Object:  User [VALUser]    Script Date: 03/16/2011 09:21:50 ******/

CREATE USER [VALUser] FOR LOGIN [VALUser] WITH DEFAULT_SCHEMA=[VALUser]

GO

/****** Object:  Schema [VALUser]    Script Date: 03/16/2011 09:21:32 ******/

CREATE SCHEMA [VALUser] AUTHORIZATION [VALUser]

GO

/****** Object:  UserDefinedDataType [dbo].[ShortDescription]    Script Date: 03/16/2011 09:21:50 ******/

CREATE TYPE [dbo].[ShortDescription] FROM [varchar](50) NOT NULL

GO

/****** Object:  UserDefinedDataType [dbo].[Identifier]    Script Date: 03/16/2011 09:21:50 ******/

CREATE TYPE [dbo].[Identifier] FROM [int] NOT NULL

GO

/****** Object:  StoredProcedure [dbo].[maint_TidyDatabase]    Script Date: 03/16/2011 09:21:32 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE  [dbo].[maint_TidyDatabase]





AS



SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SET NOCOUNT ON



/*

__________________________________________________________________________________________

Author:			Dylan Morley  2009

Description:		



Created:		

Modification History:	Date		Modified By	Modification

			[dd/mm/yy]	[Name]		[Desc]



__________________________________________________________________________________________

*/



UPDATE

	Users

SET

	UserLoggedOn = 0,

	LoggedOnMachine = NULL



/*

IF (@@ERROR != 0)

BEGIN

	RAISERROR('', 18, 18) WITH SETERROR

	RETURN @@ERROR

END

*/



-- Return any error codes \ Reset the NOCOUNT property.

RETURN @@ERROR

SET NOCOUNT OFF

GO

/****** Object:  Table [dbo].[Users]    Script Date: 03/16/2011 09:21:50 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Users](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[WindowsIdentityName] [dbo].[ShortDescription] NOT NULL,

	[Forename] [dbo].[ShortDescription] NOT NULL,

	[Surname] [dbo].[ShortDescription] NOT NULL,

	[IsActive] [bit] NOT NULL CONSTRAINT [DF_Users_UserIsActive]  DEFAULT ((0)),

 CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_WindowsIdentity] ON [dbo].[Users] 

(

	[WindowsIdentityName] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[FileType]    Script Date: 03/16/2011 09:21:38 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[FileType](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[Description] [dbo].[ShortDescription] NULL,

 CONSTRAINT [PK_FileTypes] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Files]    Script Date: 03/16/2011 09:21:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Files](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[Name] [dbo].[ShortDescription] NOT NULL,

	[Path] [varchar](150) NOT NULL,

	[Description] [dbo].[ShortDescription] NOT NULL,

	[DistributeTo] [varchar](150) NULL,

	[DistributeToUserProfile] [bit] NOT NULL,

	[Notes] [varchar](1000) NULL,

	[Workgroup] [dbo].[ShortDescription] NULL,

	[Command] [dbo].[ShortDescription] NULL,

	[FileTypeID] [dbo].[Identifier] NOT NULL,

	[IsActive] [bit] NOT NULL,

	[Icon] [image] NULL,

 CONSTRAINT [PK_Files] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[GroupType]    Script Date: 03/16/2011 09:21:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[GroupType](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[Description] [dbo].[ShortDescription] NULL,

 CONSTRAINT [PK_GroupTypes] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[Groups]    Script Date: 03/16/2011 09:21:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Groups](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[Description] [dbo].[ShortDescription] NOT NULL,

	[GroupTypeID] [dbo].[Identifier] NOT NULL CONSTRAINT [DF_groups_grp_group_type_id_fk]  DEFAULT ((0)),

	[IsGroupActive] [bit] NOT NULL CONSTRAINT [DF_Groups_GroupIsActive]  DEFAULT ((0)),

 CONSTRAINT [PK_Groups] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[UserActivity]    Script Date: 03/16/2011 09:21:47 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[UserActivity](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[UserID] [int] NULL,

	[FileID] [int] NULL,

	[LaunchDatetime] [smalldatetime] NULL,

	[LaunchedFrom] [dbo].[ShortDescription] NULL,

 CONSTRAINT [PK_User_Activity] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

/****** Object:  Table [dbo].[GroupUsers]    Script Date: 03/16/2011 09:21:43 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[GroupUsers](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[GroupID] [int] NULL,

	[UserID] [int] NULL,

 CONSTRAINT [PK_Group_Users] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],

 CONSTRAINT [IX_Group_Users] UNIQUE NONCLUSTERED 

(

	[GroupID] ASC,

	[UserID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  Table [dbo].[Permissions]    Script Date: 03/16/2011 09:21:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Permissions](

	[Id] [dbo].[Identifier] IDENTITY(1,1) NOT NULL,

	[GroupID] [int] NOT NULL,

	[FileID] [int] NOT NULL,

 CONSTRAINT [PK_Permissions] PRIMARY KEY CLUSTERED 

(

	[Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

/****** Object:  ForeignKey [FK_Files_FileTypes]    Script Date: 03/16/2011 09:21:36 ******/

ALTER TABLE [dbo].[Files]  WITH NOCHECK ADD  CONSTRAINT [FK_Files_FileTypes] FOREIGN KEY([FileTypeID])

REFERENCES [dbo].[FileType] ([Id])

GO

ALTER TABLE [dbo].[Files] CHECK CONSTRAINT [FK_Files_FileTypes]

GO

/****** Object:  ForeignKey [FK_Groups_GroupTypes]    Script Date: 03/16/2011 09:21:40 ******/

ALTER TABLE [dbo].[Groups]  WITH NOCHECK ADD  CONSTRAINT [FK_Groups_GroupTypes] FOREIGN KEY([GroupTypeID])

REFERENCES [dbo].[GroupType] ([Id])

GO

ALTER TABLE [dbo].[Groups] CHECK CONSTRAINT [FK_Groups_GroupTypes]

GO

/****** Object:  ForeignKey [FK_Group_Users_Groups]    Script Date: 03/16/2011 09:21:43 ******/

ALTER TABLE [dbo].[GroupUsers]  WITH NOCHECK ADD  CONSTRAINT [FK_Group_Users_Groups] FOREIGN KEY([GroupID])

REFERENCES [dbo].[Groups] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_Group_Users_Groups]

GO

/****** Object:  ForeignKey [FK_Group_Users_Users]    Script Date: 03/16/2011 09:21:43 ******/

ALTER TABLE [dbo].[GroupUsers]  WITH NOCHECK ADD  CONSTRAINT [FK_Group_Users_Users] FOREIGN KEY([UserID])

REFERENCES [dbo].[Users] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[GroupUsers] CHECK CONSTRAINT [FK_Group_Users_Users]

GO

/****** Object:  ForeignKey [FK_Permissions_Files]    Script Date: 03/16/2011 09:21:45 ******/

ALTER TABLE [dbo].[Permissions]  WITH CHECK ADD  CONSTRAINT [FK_Permissions_Files] FOREIGN KEY([FileID])

REFERENCES [dbo].[Files] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Permissions] CHECK CONSTRAINT [FK_Permissions_Files]

GO

/****** Object:  ForeignKey [FK_Permissions_Groups]    Script Date: 03/16/2011 09:21:45 ******/

ALTER TABLE [dbo].[Permissions]  WITH NOCHECK ADD  CONSTRAINT [FK_Permissions_Groups] FOREIGN KEY([GroupID])

REFERENCES [dbo].[Groups] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[Permissions] CHECK CONSTRAINT [FK_Permissions_Groups]

GO

/****** Object:  ForeignKey [FK_User_Activity_Files]    Script Date: 03/16/2011 09:21:47 ******/

ALTER TABLE [dbo].[UserActivity]  WITH NOCHECK ADD  CONSTRAINT [FK_User_Activity_Files] FOREIGN KEY([FileID])

REFERENCES [dbo].[Files] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[UserActivity] CHECK CONSTRAINT [FK_User_Activity_Files]

GO

/****** Object:  ForeignKey [FK_User_Activity_Users]    Script Date: 03/16/2011 09:21:47 ******/

ALTER TABLE [dbo].[UserActivity]  WITH NOCHECK ADD  CONSTRAINT [FK_User_Activity_Users] FOREIGN KEY([UserID])

REFERENCES [dbo].[Users] ([Id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[UserActivity] CHECK CONSTRAINT [FK_User_Activity_Users]

GO



/****** DEFAULT DATA SCRIPTING ******/



USE [VAL]

GO



/****** Object:  Table [dbo].[GroupType]    Script Date: 03/21/2011 13:54:00 ******/

SET IDENTITY_INSERT [dbo].[GroupType] ON

INSERT [dbo].[GroupType] ([Id], [Description]) VALUES (0, N'None Specified')

SET IDENTITY_INSERT [dbo].[GroupType] OFF

/****** Object:  Table [dbo].[FileType]    Script Date: 03/21/2011 13:54:00 ******/

SET IDENTITY_INSERT [dbo].[FileType] ON

INSERT [dbo].[FileType] ([Id], [Description]) VALUES (1, N'Executable')

INSERT [dbo].[FileType] ([Id], [Description]) VALUES (2, N'Database')

INSERT [dbo].[FileType] ([Id], [Description]) VALUES (3, N'Miscellaneous')

SET IDENTITY_INSERT [dbo].[FileType] OFF





EXEC sp_addrolemember 'db_datareader', 'VALUser'

EXEC sp_addrolemember 'db_datawriter', 'VALUser'