Web-Service: How to authenticate users with the SQL Server database table using VB
-- Sample Table Creation and Index Creation script for MCIS-4423
-- This script is designed to be "re-runnable", but you need to be careful,
-- Since this will DROP the table, which would be bad if it was an existing table with data

-- Make sure you are in the correct database
USE [AdventureWorks]
GO

-- Drop Check Constraint
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[CK_Team_TeamID]') AND parent_object_id = OBJECT_ID(N'[dbo].[Team]'))
ALTER TABLE [dbo].[Team] DROP CONSTRAINT [CK_Team_TeamID]
GO

-- Drop Table
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND type in (N'U'))
DROP TABLE [dbo].[Team]
GO

-- Create Table
CREATE TABLE [dbo].[Team](
    [TeamID] [char](3) NOT NULL,
    [TeamName] [varchar](20) NOT NULL,
    [City] [varchar](50) NOT NULL,
    [StateCode] [char](2) NULL,
    [PostalCode] [char](5) NULL,
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
    [TeamID] 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

-- Add Check Constraint
ALTER TABLE [dbo].[Team]  WITH CHECK ADD  CONSTRAINT [CK_Team_TeamID] CHECK  (([TeamID] like '[A-Z][A-Z][A-Z]'))
GO
ALTER TABLE [dbo].[Team] CHECK CONSTRAINT [CK_Team_TeamID]
GO

-- Drop index if it exists
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[Team]') AND name = N'IX_Team_TeamName')
DROP INDEX [IX_Team_TeamName] ON [dbo].[Team] WITH ( ONLINE = OFF )
GO

-- Add non-clustered index on StateCode column
-- Use ONLINE = ON if you have Developer or Enterprise Edition
-- Use MAXDOP = 2 (set to roughly 25% of the number of CPU cores to keep index creation from affecting performance)
CREATE NONCLUSTERED INDEX [IX_Team_TeamName] ON [dbo].[Team]
(
    [StateCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, MAXDOP = 2, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

SELECT username FROM users
WHERE username=@username and password=@password