Introduction
In this article we will show you how to make Database and Tables for Advanced Login System in MS SQL Server 2012
Prerequisites
Visual Studio 2010/2012/2013/15/17, SQL Server 2005/08/2012
Project used Version
Visual Studio 2013, SQL Server 2012
Create Tables in MS SQL Server
Open MS SQL Express 2012, Right Click on Databases, Create New Database as "DBLoginSystem".
Create New Tables {Users} and {Roles} and make relationship between Roles.RoleID and Users.RoleID as shown below
or
Paste below SQL script to create tables.
User Table
Role Table
Relationship Between Table Users and Roles
SQL Querry - Create User Table
USE [DBLoginSystem]
GO
/****** Object: Table [dbo].[Users] Script Date: 01-12-2018 8.41.14 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Users](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NOT NULL,
[Password] [varchar](50) NOT NULL,
[Email] [varchar](50) NOT NULL,
[RetryAttempts] [int] NULL,
[IsActivated] [bit] NULL,
[IsLocked] [bit] NULL,
[DeactivatedDateTime] [datetime] NULL,
[LockedDateTime] [datetime] NULL,
[CreatedDate] [datetime] NULL,
[LastLogin] [datetime] NULL,
[RoleID] [int] NOT NULL,
[SecretQuestion] [varchar](50) NULL,
[SecretAnswer] [nchar](10) NULL,
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
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Roles] ([RoleID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Roles]
GO
SQL Querry- Create Roles Table
USE [DBLoginSystem]
GO
/****** Object: Table [dbo].[Roles] Script Date: 01-12-2018 8.41.56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Roles](
[RoleID] [int] IDENTITY(1,1) NOT NULL,
[RoleName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED
(
[RoleID] 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
SQL Querry- Make Relationship between Tables
USE [DBLoginSystem]
GO
ALTER TABLE [dbo].[Users] WITH CHECK ADD CONSTRAINT [FK_Users_Roles] FOREIGN KEY([RoleID])
REFERENCES [dbo].[Roles] ([RoleID])
GO
ALTER TABLE [dbo].[Users] CHECK CONSTRAINT [FK_Users_Roles]
GO
Create Tables Manually and Make a Relationship Between Them
Step:1
Create Table Roles
Add columns and select Data Type as shown below:
Set UserID and RoleID as primary key:
Set Auto Increment column option for UserID and RoleID as shown below:
Save table as Users and Roles
Create Relationship between table Users.RoleID to Roles.RoleID as shown:
Click on new button and browse shown below.
Select primary key column in Roles.RoleID table and Foreign Key column in Users.RoleID
Download Complete Source Code C#