User Login

Question

User Login

aturner51

SSC-Addicted

Points: 448
More actions
October 7, 2004 at 10:48 am

#60534

I'm trying to create a stored procedure based upon a user login. The criteria requires that when a user login, it validates against the Roles table as to whether or not they are a "1-Receiptor", "2-Supervisor", "3-Administrator"; and secondly, that it is based also on the OfficeName from the Offices table.  When a user logs in, it should know what role and department they are assigned to.

When the page appears in the browser, I want only the Username and Password to appear. Their role and office assignment will not displayed unless their account is being updated by a supervisor or administrator.

I really tried to take a shot at writing the stored procedure, but I'm pretty sure I missed the mark.    Can you help?   I also need to add the ability to check for errors. (See below)

Here is the Role and Office table:
CREATE TABLE [dbo].[Offices] (
                [OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
                [OfficeName] [varchar] (35) NOT NULL ,
                [OfficeCostCtr] [varchar] (5) NOT NULL ,
                [CityID] [int] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Roles] (
                [RoleID] [int] IDENTITY (1, 1) NOT NULL ,
                [Role] [varchar] (15) NOT NULL
) ON [PRIMARY]
GO

Here's my stored procedure:
CREATE PROCEDURE dbo.spCheckUserLogin
@Username char(10),
@Password char(8)
as
declare @userid int
declare @RoleId int
declare @OfficeID int
select @RoleID = UserId
from   Users
where RoleID = 1 /* Receiptor */
OR RoleID = 2 /* Supervisor */
OR RoleID = 3 /* Administrator */
/* valid the office */
select @OfficeID = UserID
from Users
INNER JOIN Offices ON Offices.OfficeID = Users.OfficeID
select @userid = null
/* validate the user */
select @userid = UserId
from   Users
where Username = @Username
and    Password = @Password
if @userid is not null
begin
if @userid <> @RoleId
begin
    select @userid = null
    /* validate the user belongs to the offices and roles */
    select @userid = Users.UserId
    from   Roles, Offices
    inner join Users on UserRoles.UserId = Users.UserId
    where RoleID = @RoleID
    and    Username = @Username
    and    Password = @Password
    OR RoleName = 'Receiptor'
    OR RoleName = 'Supervisor'
    OR RoleName = 'Administrator'
    if not @userid is null
    begin
      update UserRoles
      set    LastLoginDate = getdate()
      where UserId = @userid
      and    RoleID = @RoleID
      and    OfficeId = @OfficeID
    end
end
end
select 'UserId' = @userid
GO

Sample:

User ID       UserName       Password       RoleID                  OfficeID
1                aturner             password         3 (Administrator)     Accounting
2                tbrown             greatthan          1 (Receiptor)          HumanResources
3                desmith           rowdy               2 (Supervisor)         Accounting Manager

** The roleID only displays the integer, the names in parenthesis indicates the type of role.

Desperately needing help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Barry Cummings Newbie Points: 4 More actions · Answer 1

I tried to recreate this, but couldn't figure out exactly what the structure was of the other tables involved (e.g. Users and UserRoles). Can you add the CREATE TABLE scripts for these too please, then I'll take another stab at it.

Best Regards,

Barry Cummings

aturner51 SSC-Addicted Points: 448 More actions · Answer 2

Here's the script file. I wasn't sure if I could import the .sql file into this post, so I copied the file below.

Note: If there is a way to upload the file, please let me know. Thanks in advance.

CREATE TABLE

[dbo].[Employees] (

[EmpLID] [char] (11)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Last_Name] [char] (30)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[First_Name] [char] (30)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Business_Unit] [char] (5)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Empl_Status] [char] (1)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[JobTitle] [char] (30)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[DeptName] [char] (30)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

)

ON [PRIMARY]

GO

CREATE TABLE

[dbo].[Offices] (

[OfficeID] [int]

IDENTITY (1, 1) NOT NULL ,

[OfficeName] [varchar] (35)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[OfficeCostCtr] [varchar] (5)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[CityID] [int]

NULL

)

ON [PRIMARY]

GO

CREATE TABLE

[dbo].[Roles] (

[RoleID] [int]

IDENTITY (1, 1) NOT NULL ,

[Role] [varchar] (15)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL

)

ON [PRIMARY]

GO

CREATE TABLE

[dbo].[UserRoles] (

[UserRoleID] [int]

IDENTITY (1, 1) NOT NULL ,

[UserID] [int]

NOT NULL ,

[RoleID] [int]

NOT NULL ,

[ExpiredDate] [datetime]

NULL

)

ON [PRIMARY]

GO

CREATE TABLE

[dbo].[Users] (

[UserID] [int]

IDENTITY (1, 1) NOT NULL ,

[UserName] [char] (10)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Password] [char] (8)

COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[RoleID] [int]

NULL ,

[DateCreated] [datetime]

NULL ,

[DateLastLogin] [datetime]

NULL ,

[InActiveDate] [datetime]

NULL

)

ON [PRIMARY]

GO

ALTER TABLE

[dbo].[Offices] WITH NOCHECK ADD

CONSTRAINT [PK_Offices] PRIMARY KEY CLUSTERED

(

[OfficeID]

)

ON [PRIMARY]

GO

ALTER TABLE

[dbo].[Roles] WITH NOCHECK ADD

CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED

(

[RoleID]

)

ON [PRIMARY]

GO

ALTER TABLE

[dbo].[UserRoles] WITH NOCHECK ADD

CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED

(

[UserRoleID]

)

ON [PRIMARY]

GO

ALTER TABLE

[dbo].[Users] WITH NOCHECK ADD

CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED

(

[UserID]

)

ON [PRIMARY]

GO

ALTER TABLE

[dbo].[Users] WITH NOCHECK ADD

CONSTRAINT [DF_Users_DateCreated] DEFAULT (getdate()) FOR [DateCreated],

CONSTRAINT [DF_Users_DateLastLogin] DEFAULT (getdate()) FOR [DateLastLogin]

GO

CREATE View

dbo.vwSelect_Employees

AS

SELECT

EmpID,

EmpLID,

Last_Name,

First_Name,

Business_Unit,

Empl_Status,

JobTitle,

DeptName

FROM

[Employees]

CREATE View

dbo.vwSelect_Offices

AS

SELECT

OfficeID,

SUBSTRING(OfficeCostCtr, 3, 3) + ' -- ' + OfficeName AS "Office Cost Center",

CityID

FROM

[Offices]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE View

dbo.vwSelect_Roles

AS

SELECT

RoleID,

Role

FROM

[Roles]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE View

dbo.vwSelect_UserRoles

AS

SELECT

UserRoleID,

UserID,

RoleID,

ExpiredDate

FROM

[UserRoles]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDelete_Users

@user-id

int

AS

DELETE

[Users]

WHERE

UserID = @user-id

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropDown_Employees_Select

AS

SET

CONCAT_NULL_YIELDS_NULL OFF

SET NOCOUNT ON

SELECT

(

RTRIM(EmplID)) AS EmpID, (LTRIM(RTRIM(Last_Name))) + ', ' + (LTRIM(RTRIM(First_Name))) AS Employee,

Business_Unit = 'SBCSD',

Empl_Status = 'A',

JobTitle,

DeptName

FROM

Employees

WHERE

Business_Unit <> 'VLNTR'

ORDER BY

Last_Name ASC

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_AuditLog

AS

SELECT

AuditID,

ReceiptID,

EmpID,

AccessDate

FROM

[AuditLog]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_Cities

AS

SELECT

CityID,

Cities

FROM

[Cities]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_Classification

AS

SELECT

ClassID,

ClassName

FROM

[Classification]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_Employees

AS

SELECT

EmpID,

EmpLID,

Last_Name,

First_Name,

Business_Unit,

Empl_Status,

JobTitle,

DeptName

FROM

[Employees]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_FeeTypes

AS

SELECT

FeeTypeID,

FeeType,

FeeCost,

InActiveDate

FROM

[FeeTypes]

ORDER BY

FeeType ASC

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_Offices

AS

SELECT

OfficeID,

SUBSTRING(OfficeCostCtr, 3, 3) + ' -- ' + OfficeName AS "Office Cost Center",

CityID

FROM

[Offices]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_Roles

AS

--

-- Created 10/6/2004 by Alice Turner, Programmer Analyst II

--

SELECT

RoleID,

Role

FROM

[Roles]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_UserRoles

AS

SELECT

UserRoleID,

UserID,

RoleID,

ExpiredDate

FROM

[UserRoles]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spDropdown_Users

AS

SELECT

UserID,

UserName,

Password,

RoleID,

DateCreated,

DateLastLogin,

InActiveDate

FROM

[Users]

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spInsert_Roles

@RoleID

int out,

@Role

varchar(15)

AS

INSERT INTO

[Roles] (

Role)

Values

(

@Role)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spInsert_UserRoles

@UserRoleID

int out,

@user-id

int,

@RoleID

int,

@ExpiredDate

datetime

AS

INSERT INTO

[UserRoles] (

UserID,

RoleID,

ExpiredDate)

Values

(

@user-id,

@RoleID,

@ExpiredDate)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spInsert_Users

@user-id

int out,

@UserName

char(10),

@Password

char(8),

@RoleID

int,

@DateCreated

datetime out,

@DateLastLogin

datetime out,

@InActiveDate

datetime

AS

IF

@DateCreated Is Null

SET @DateCreated = (getdate())

IF

@DateLastLogin Is Null

SET @DateLastLogin = (getdate())

INSERT INTO

[Users] (

UserName,

Password,

RoleID,

DateCreated,

DateLastLogin,

InActiveDate)

Values

(

@UserName,

@Password,

@RoleID,

@DateCreated,

@DateLastLogin,

@InActiveDate)

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spUpdate_Employees

@EmpID

int,

@EmpLID

char(11),

@Last_Name

char(30),

@First_Name

char(30),

@Business_Unit

char(5),

@Empl_Status

char(1),

@JobTitle

char(30),

@DeptName

char(30),

@ORIGINAL_EmpID

int

AS

UPDATE

[Employees] SET

EmpLID = @EmpLID,

Last_Name = @Last_Name,

First_Name = @First_Name,

Business_Unit = @Business_Unit,

Empl_Status = @Empl_Status,

JobTitle = @JobTitle,

DeptName = @DeptName

WHERE

EmpID = @ORIGINAL_EmpID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spUpdate_Offices

@OfficeID

int,

@OfficeName

varchar(35),

@OfficeCostCtr

varchar(5),

@CityID

int,

@ORIGINAL_OfficeID

int

AS

UPDATE

[Offices] SET

OfficeName = @OfficeName,

OfficeCostCtr = @OfficeCostCtr,

CityID = @CityID

WHERE

OfficeID = @ORIGINAL_OfficeID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spUpdate_Roles

@RoleID

int,

@Role

varchar(15),

@ORIGINAL_RoleID

int

AS

UPDATE

[Roles] SET

Role = @Role

WHERE

RoleID = @ORIGINAL_RoleID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spUpdate_UserRoles

@UserRoleID

int,

@user-id

int,

@RoleID

int,

@ExpiredDate

datetime,

@ORIGINAL_UserRoleID

int

AS

UPDATE

[UserRoles] SET

UserID = @user-id,

RoleID = @RoleID,

ExpiredDate = @ExpiredDate

WHERE

UserRoleID = @ORIGINAL_UserRoleID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS OFF

GO

CREATE PROCEDURE

dbo.spUpdate_Users

@user-id

int,

@UserName

char(10),

@Password

char(8),

@RoleID

int,

@DateCreated

datetime,

@DateLastLogin

datetime,

@InActiveDate

datetime,

@ORIGINAL_UserID

int

AS

UPDATE

[Users] SET

UserName = @UserName,

Password = @Password,

RoleID = @RoleID,

DateCreated = @DateCreated,

DateLastLogin = @DateLastLogin,

InActiveDate = @InActiveDate

WHERE

UserID = @ORIGINAL_UserID

GO

SET QUOTED_IDENTIFIER OFF

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET

ANSI_NULLS ON

GO

bledu SSChampion Points: 12335 More actions · Answer 3

since you have created a table dbo.UserRoles there is no need to to have a roleid column in the user table.

you need to create a table dbo.UserOffice that will link the user and the office.

CREATE TABLE [dbo].[UserOffice] (

[UserOfficeID] [int] IDENTITY (1, 1) NOT NULL ,

[UserID] [int] NOT NULL ,

[OfficeID] [int] NOT NULL ,

[ExpiredDate] [datetime] NULL

) ON [PRIMARY]

-------------------------------

run this proc

---------------------------------

CREATE PROC spCheckUserLogin

@Username varchar(10),

@Password varchar(8)

AS

DECLARE @errno int, @errmsg varchar(255)

DECLARE @ogPwd as varchar(8)

IF EXISTS(SELECT UserName from dbo.Users where UserName = @Username)

BEGIN

--validate the user

--check password

SELECT @ogPwd = [Password]

FROM dbo.Users WHERE UserName = @Username

IF @ogPwd = @Password

BEGIN

--correct password

SELECT A.UserID,A.UserName, B.RoleID,D.OfficeName

FROM dbo.Users A INNER JOIN

dbo.UserRoles B ON B.UserID = A.UserID INNER JOIN

dbo.UserOffice C ON C.UserID = A.UserID INNER JOIN

dbo.Offices D ON D.OfficeID = C.OfficeID

WHERE A.UserName = @Username

END

ELSE

BEGIN

--wrong password

SELECT @errno = 20000,@errmsg = 'Wrong Username Or Password'

GOTO Err_Handle

END

ELSE

BEGIN

--user does not exist

SELECT @errno = 20000,@errmsg = 'The Username Does Not Exist'

GOTO Err_Handle

END

RETURN

Err_Handle:

RAISERROR @errno @errmsg

Everything you can imagine is real.

aturner51 SSC-Addicted Points: 448 More actions · Answer 4

Thank you so much for the time you spent on resolving my problem. I'm very appreciative also of the resolution you came up with. Once again, thanks.