Password at Rest

uspAddUser

USE [CCR]

GO

/****** Object:  StoredProcedure [dbo].[uspAddUser]    Script Date: 3/27/2020 4:04:23 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[uspAddUser]

    @pLogin NVARCHAR(50),

    @pPassword NVARCHAR(50),

    @pFirstName NVARCHAR(40) = NULL,

    @pLastName NVARCHAR(40) = NULL,

    @responseMessage NVARCHAR(250) OUTPUT

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @salt UNIQUEIDENTIFIER=NEWID()

    BEGIN TRY

        INSERT INTO dbo.[CCR_User] (LoginName, PasswordHash, Salt, FirstName, LastName)

        VALUES(@pLogin, HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36))), @salt, @pFirstName, @pLastName)

       SET @responseMessage='Success'

    END TRY

    BEGIN CATCH

        SET @responseMessage=ERROR_MESSAGE()

    END CATCH

END

uspLogin

USE [CCR]

GO

/****** Object:  StoredProcedure [dbo].[uspLogin]    Script Date: 3/27/2020 4:04:57 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[uspLogin]

    @pLoginName NVARCHAR(254),

    @pPassword NVARCHAR(50),

    @responseMessage NVARCHAR(250)='' OUTPUT

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @userID INT

    IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[CCR_User] WHERE LoginName=@pLoginName)

    BEGIN

        SET @userID=(SELECT UserID FROM [dbo].[CCR_User] WHERE LoginName=@pLoginName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))

       IF(@userID IS NULL)

           SET @responseMessage='0' --'Incorrect password'

       ELSE

           SET @responseMessage='1'--'User successfully logged in'

    END

    ELSE

       SET @responseMessage='0'--'Invalid login'

END

uspLoginFun  (as a function for queries)

USE [CCR]

GO

/****** Object:  UserDefinedFunction [dbo].[uspLoginFun]    Script Date: 3/27/2020 4:07:38 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER FUNCTION [dbo].[uspLoginFun]

(

    @pLoginName NVARCHAR(254),

    @pPassword NVARCHAR(50)

)

     RETURNS nvarchar(1)

AS

BEGIN

    --SET NOCOUNT ON

    DECLARE @userID INT,

             @responseMessage NVARCHAR(1)

    IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[CCR_User] WHERE LoginName=@pLoginName)

    BEGIN

        SET @userID=(SELECT UserID FROM [dbo].[CCR_User] WHERE LoginName=@pLoginName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))

       IF(@userID IS NULL)

           --SET @responseMessage='0' --'Incorrect password'

            RETURN '0'

       ELSE

           --SET @responseMessage='1'--'User successfully logged in'

            RETURN '1'

    END

    ELSE

       --SET @responseMessage='0'--'Invalid login'

       RETURN '0'

  RETURN '0' 

END

--RETURN '0'

uspUpdateUser

USE [CCR]

GO

/****** Object:  StoredProcedure [dbo].[uspUpdateUser]    Script Date: 3/27/2020 4:05:17 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[uspUpdateUser]

    @pLogin NVARCHAR(50),

    @pPassword NVARCHAR(50)

AS

BEGIN

    SET NOCOUNT ON

    DECLARE @salt UNIQUEIDENTIFIER=NEWID()

    --BEGIN TRY

       UPDATE dbo.[CCR_User]  SET PasswordHash= HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36))),Salt=@salt

        WHERE LoginName=@pLogin

        UPDATE [CCR].[dbo].[CCR_UserBase] SET ChangeNext = 0, dtLastModified = GETDATE() WHERE [Login]=@pLogin

        --INSERT INTO dbo.[CCR_User] (LoginName, PasswordHash, Salt, FirstName, LastName)

        --VALUES(@pLogin, HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36))), @salt, @pFirstName, @pLastName)

       --SET @responseMessage='Success'

    --END TRY

    --BEGIN CATCH

    --    SET @responseMessage=ERROR_MESSAGE()

--  END CATCH

END

     --exec [dbo].[uspUpdateUser] @pLogin='richjlemmermann',@pPassword='new'

https://www.mssqltips.com/sqlservertip/4037/storing-passwords-in-a-secure-way-in-a-sql-server-database/

ProperCase Function for Strings

USE [VisualCactus]

GO

/****** Object:  UserDefinedFunction [dbo].[ProperCaseSimple]    Script Date: 3/24/2020 12:27:51 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

create function [dbo].[ProperCaseSimple](@Text as varchar(8000))

returns varchar(8000)

as

begin

   declare @Reset bit;

   declare @Ret varchar(8000);

   declare @i int;

   declare @c char(1);

   select @Reset = 1, @i=1, @Ret = '';

   while (@i <= len(@Text))

       select @c= substring(@Text,@i,1),

               @Ret = @Ret + case when @Reset=1 then UPPER(@c) else LOWER(@c) end,

               @Reset = case when @c like '[a-zA-Z]' then 0 else 1 end,

               @i = @i +1

   return @Ret

end

GO