Stored Procedure to Track user Login

CREATE PROCEDURE [dbo].[AddUser] 
    -- Add the parameters for the stored procedure here
    @NewLogin NVARCHAR(255), 
    @NewPassword NVARCHAR(32), 
    @Identifier UNIQUEIDENTIFIER output,
    @ResponseMessage NVARCHAR(128) output

AS
BEGIN
    declare @MyUserID int;
    declare @MyIdentifier UNIQUEIDENTIFIER;

    -- Remove leading and trailing spaces
    -- make Null if the remainder is an empty string
    declare @MyLogin NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@NewLogin)), '')
    declare @MyPassword NVARCHAR(255) = NULLIF(LTRIM(RTRIM(@NewPassword)), '')

    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    BEGIN TRY
    if (@MyLogin is null) or (@MyPassword is null)
        begin -- Login and Password are  blank
            SET @ResponseMessage='Login and password can not be blank';
            SET @Identifier = null;
        end -- Login and Password are  blank
    else
        begin -- Login and Password are not blank
            -- see if the logon already existed 
            SET @MyUserID=(SELECT ID from dbo.[User] where Login=LOWER(@MyLogin))
            If @MyUserID is not NULL
               begin -- User existed
                  SET @MyIdentifier = (Select Identifier FROM [dbo].[User] WHERE Login=LOWER(@MyLogin) AND PasswordHash=HASHBYTES('SHA2_512', @MyPassword+CAST(Salt AS NVARCHAR(36))))
                  IF @MyIdentifier IS NULL
                     begin
                        SET @ResponseMessage='Incorrect password';
                     end
                  else
                     begin
                        SET @Identifier = @MyIdentifier;
                        SET @ResponseMessage='User successfully logged in';
                     end
                  Insert into [dbo].[UserLogonHistory] ([UserID],[Message]) Values (@MyUserID, @ResponseMessage)
               end -- User existed
            else
               begin -- User does not exist
                  SET @ResponseMessage='Invalid login';
                  SET @Identifier = null;
               end -- User does not exist
       end -- Login and Password are not blank
   END TRY
   BEGIN CATCH
      SET @ResponseMessage=ERROR_MESSAGE();
      SET @Identifier = null;
   END CATCH
END

GO

Post a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s