SQL user data store tables and stored procedures configuration

Applies to Identity Platform 24.04 or later

Use this guide as a reference to create SQL tables and stored procedures that are needed for SecureAuth® Identity Platform to utilize the SQL database for membership and/or profile information.

Prerequisites

Important: Before you upgrade to the Identity Platform release 23.07 or later, you must update your SQL data store
To learn about this important update, see Upgrade information for SQL data stores.
SecureAuth Identity Platform release 24.04 or later
SQL user data store
SQL user data store added in the Identity Platform
Ensure ports are open to enable connection to the SQL user data store
Basic SQL knowledge to deploy the scripts below

Note

If the JSON Data Format is selected for Device Profiles, Push Notification Tokens, HOTP Token, or Access Histories Profile Fields, then the JSON script must be executed for Create Object Table Type, Get User Profile Stored Procedure, Update User Profile Stored Procedure, and Create Database operations.

See a screenshot of Profile Fields with JSON Data Format selected...

DELETE User

To delete an end user from the SQL server data store, use the Delete user API endpoint.

SQL Table Samples

User Table

Single table contains the User ID, Password, and Profile Information.

CREATE TABLE

CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](128) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](512) NULL,
	[AuxID2] [varchar](512) NULL,
	[AuxID3] [varchar](512) NULL,
	[AuxID4] [varchar](512) NULL,
	[AuxID5] [varchar](512) NULL,
	[AuxID6] [varchar](512) NULL,
	[AuxID7] [varchar](512) NULL,
	[AuxID8] [varchar](512) NULL,
	[AuxID9] [varchar](512) NULL,
	[AuxID10] [varchar](512) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [varchar](50) NULL,
	[CertCount] [varchar](50) NULL,
	[MobileResetDate] [varchar](50) NULL,
	[MobileCount] [varchar](50) NULL,
	[ExtSyncPwdDate] [varchar](50) NULL,
	[OATHSeed] [varchar](MAX) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL,
	[AccountStatus] [varchar](50) NULL,
	[CookieKeys] [varchar](1000) NULL,
	[UniqueUserId] [uniqueidentifier] NULL,
	[MultiFactorThrottle] [varchar](512) NULL,
	[PWThrottle] [varchar](512) NULL,
	[OTPValidation] [varchar](512) NULL,
	[OIDCConsent] [varchar](512) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO

CREATE TABLE (JSON)

CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](128) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](1000) NULL,
	[AuxID2] [varchar](1000) NULL,
	[AuxID3] [varchar](1000) NULL,
	[AuxID4] [varchar](1000) NULL,
	[AuxID5] [varchar](1000) NULL,
	[AuxID6] [varchar](1000) NULL,
	[AuxID7] [varchar](1000) NULL,
	[AuxID8] [varchar](1000) NULL,
	[AuxID9] [varchar](1000) NULL,
	[AuxID10] [varchar](1000) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [varchar](50) NULL,
	[CertCount] [varchar](50) NULL,
	[MobileResetDate] [varchar](50) NULL,
	[MobileCount] [varchar](50) NULL,
	[ExtSyncPwdDate] [varchar](50) NULL,
	[OATHSeed] [varchar](MAX) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[CookieKeys] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL,
	[AccountStatus] [varchar](50) NULL,
	[UniqueUserId] [uniqueidentifier] NULL,
	[MultiFactorThrottle] [varchar](1000) NULL,
	[OTPValidation] [varchar](1000) NULL,
	[PWThrottle] [varchar](1000) NULL,
	[OIDCConsent] [varchar](1000) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO

Object Table Type

Script to create the Object Table Type used for Fingerprint and Push Notification token information.

CREATE TABLE Type

CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO

CREATE TABLE Type (JSON)

Use this script to create the Object Table Type when Fingerprint and Push Notification token information is configured in JSON.

USE [UserStore]
GO

CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO

CREATE TYPE [dbo].[JsonTable] AS TABLE( [ObjectValue] [varchar](max) NULL)
GO

Fingerprint Table

Table contains Browser / Device Fingerprinting information for each user.

CREATE TABLE for Browser / Device Fingerprinting

CREATE TABLE [dbo].[UserFP] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFP (UserName)
GO

CREATE TABLE (JSON) for Browser / Device Fingerprinting

CREATE TABLE [dbo].[UserFPJson] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFPJson (UserName)
GO

Push Notification Table

Table contains Push Notification token information for each user.

CREATE TABLE for Push Notification

CREATE TABLE [dbo].[UserPN] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPN (UserName)
GO

CREATE TABLE (JSON) for Push Notification

CREATE TABLE [dbo].[UserPNJson] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPNJson (UserName)
GO

Access History Table

Table contains Access History information for each user

CREATE TABLE for User Access History

CREATE TABLE [dbo].[UserAccessHistory] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
GO

CREATE TABLE (JSON) for User Access History

CREATE TABLE [dbo].[UserAccessHistoryJson] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistoryJson (UserName)
GO

OATH Token Table

CREATE TABLE for User OATH Token

CREATE TABLE [dbo].[UserOT] (
	[UserName] [varchar](60) NOT NULL
	,[OATHToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOT (UserName)
GO

CREATE TABLE (JSON) for User OATH Token

CREATE TABLE [dbo].[UserOTJson] (
	[UserName] [varchar](60) NOT NULL
	,[OATHToken] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOTJson (UserName)
GO

Indexes

Improve response times from SQL by having the tables indexed.

CREATE Indexes

create nonclustered index UserIndex ON UserStore.dbo.UserTable (UserName)
create nonclustered index FPIndex ON UserStore.dbo.UserFP (UserName)
create nonclustered index OTIndex ON UserStore.dbo.UserOT (UserName)
create nonclustered index AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
create nonclustered index PNIndex ON UserStore.dbo.UserPN (UserName)

CREATE Indexes (JSON)

create nonclustered index UserIndex ON UserStore.dbo.UserTable (UserName)
create nonclustered index FPIndex ON UserStore.dbo.UserFPJson (UserName)
create nonclustered index OTIndex ON UserStore.dbo.UserOTJson (UserName)
create nonclustered index AccessIndex ON UserStore.dbo.UserAccessHistoryJson (UserName)
create nonclustered index PNIndex ON UserStore.dbo.UserPNJson (UserName)

Stored procedure samples - Membership

The following are sample stored procedures for use with the Identity Platform SQL Membership providers.

Get User

Checks for existing username and returns the same username when it exists.

CREATE PROCEDURE Get UserName

CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
        SELECT [UserName]
                ,ISNULL([GroupList], '')
                ,ISNULL([PwdLastSet],'1/1/1900')
                ,ISNULL([AccountStatus], '')
        FROM UserTable
        WHERE UserName = @UserName
END

Reset Password

Resets the password for the given user.

CREATE PROCEDURE Reset Password

CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
    UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
    IF @@ROWCOUNT > 0
    BEGIN
        SELECT 1
    END
END
GO

Get Password with Format

Gets the password of the user for validation.

CREATE PROCEDURE Get Password with Format

CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
    @UserName varchar(60)
AS
BEGIN
    SELECT  [Password], PasswordFormat, PasswordSalt
    FROM    dbo.UserTable
    WHERE   @UserName = UserName
END
GO

Create User

Inserts the username and password into the user table, and returns a MembershipCreateStatus enumeration, with zero (0) signaling success.

For more information about adding new enumeration values to the Create User stored procedure, see MembershipCreateStatus Enum.

CREATE PROCEDURE Create User

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat,NEWID())
 
IF @@ROWCOUNT > 0 
SELECT @Status = 0
ELSE
SELECT @Status = 1
END

Update Group List

Used on the Create User page to update the GroupList column for a user.

CREATE PROCEDURE Update Group List

USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
        ,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
    SELECT UserName
    FROM UserTable
    WHERE UserName = @UserName
    )
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
    BEGIN TRY
        BEGIN TRANSACTION
 
        UPDATE UserTable
        SET GroupList = @GroupList
        WHERE UserName = @UserName
 
        COMMIT
    END TRY
 
 
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH
GO

Stored procedure samples - Profile

The stored procedure examples include all profile information. You must modify your stored procedures to match the profile information mapped to the SQL profile provider in the profile properties.

For example, to only use SQL to store and retrieve Fingerprint information, then send only the Fingerprint parameter to the Identity Platform.

Get User Profile

Retrieves the profile of the given username.

CREATE PROCEDURE Get User Profile

CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, '0') CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, '0') MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
		,IsNull(CookieKeys, '') CookieKeys
		,IsNull(GroupList, '') GroupList
		,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
		,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
    FROM UserTable
    WHERE UserName = @UserName

    SELECT DigitalFP
    FROM UserFP
    WHERE UserName = @UserName

    SELECT PNToken
    FROM UserPN
    WHERE UserName = @UserName

	SELECT AccessHistory
	FROM UserAccessHistory
	WHERE UserName = @UserName

	SELECT OATHToken
	FROM UserOT
	WHERE UserName = @UserName
END
GO

CREATE PROCEDURE Get User Profile (JSON)

CREATE PROC [dbo].[sp_GetUserProfileJson] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, '0') CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, '0') MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
        ,isNull(CookieKeys, '') CookieKeys
        ,IsNull(GroupList, '') GroupList
		,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
		,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
		,IsNull(OTPValidation, '') OTPValidation
		,IsNull(PWThrottle, '') PWThrottle
		,IsNull(OIDCConsent, '') OIDCConsent
	FROM UserTable
    WHERE UserName = @UserName

    SELECT DigitalFP
    FROM UserFPJson
    WHERE UserName = @UserName

    SELECT PNToken
    FROM UserPNJson
    WHERE UserName = @UserName

	SELECT AccessHistory
	FROM UserAccessHistoryJson
	WHERE UserName = @UserName

	SELECT OATHToken
	FROM UserOTJson
	WHERE UserName = @UserName
END
GO

Update User Profile

Updates the user profile with the given profile information.

CREATE PROCEDURE Update User Profile

CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50) = NULL
	,@LastName VARCHAR(50) = NULL
	,@Phone1 VARCHAR(60) = NULL
	,@Phone2 VARCHAR(40) = NULL
	,@Phone3 VARCHAR(40) = NULL
	,@Phone4 VARCHAR(40) = NULL
	,@Email1 VARCHAR(60) = NULL
	,@Email2 VARCHAR(60) = NULL
	,@Email3 VARCHAR(60) = NULL
	,@Email4 VARCHAR(60) = NULL
	,@AuxID1 VARCHAR(512) = NULL
	,@AuxID2 VARCHAR(512) = NULL
	,@AuxID3 VARCHAR(512) = NULL
	,@AuxID4 VARCHAR(512) = NULL
	,@AuxID5 VARCHAR(512) = NULL
	,@AuxID6 VARCHAR(512) = NULL
	,@AuxID7 VARCHAR(512) = NULL
	,@AuxID8 VARCHAR(512) = NULL
	,@AuxID9 VARCHAR(512) = NULL
	,@AuxID10 VARCHAR(512) = NULL
	,@pinHash VARCHAR(120) = NULL
	,@Questions VARCHAR(1000) = NULL
	,@Answers VARCHAR(1000) = NULL
	,@ChallengeQuestion VARCHAR(1000) = NULL
	,@ChallengeAnswer VARCHAR(1000) = NULL
	,@CertResetDate VARCHAR(50) = NULL
	,@CertSerialNumber VARCHAR(1000) = NULL
	,@CertCount VARCHAR(50) = NULL
	,@MobileResetDate VARCHAR(50) = NULL
	,@MobileCount VARCHAR(50) = NULL
	,@ExtSyncPwdDate VARCHAR(50) = NULL
	,@HardwareToken VARCHAR(1000) = NULL
	,@iOSDevices VARCHAR(1000) = NULL
	,@OATHSeed VARCHAR(MAX) = NULL
	,@OneTimeOATHList VARCHAR(1000) = NULL
	,@CookieKeys VARCHAR(1000) = NULL
	,@MultiFactorThrottle VARCHAR(512) = NULL
	,@OTPValidation VARCHAR(512) = NULL
	,@PWThrottle VARCHAR(512) = NULL
	,@OIDCConsent VARCHAR(512) = NULL
	,@DigitalFP dbo.ObjectTable READONLY
	,@PNToken dbo.ObjectTable READONLY
	,@AccessHistory dbo.ObjectTable READONLY
	,@OATHToken dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE UserTable
		SET FirstName = IsNull(@FirstName, FirstName)
			,LastName = IsNull(@LastName, LastName)
			,Phone1 = IsNull(@Phone1, Phone1)
			,Phone2 = IsNull(@Phone2, Phone2)
			,Phone3 = IsNull(@Phone3, Phone3)
			,Phone4 = IsNull(@Phone4, Phone4)
			,Email1 = IsNull(@Email1, Email1)
			,Email2 = IsNull(@Email2, Email2)
			,Email3 = IsNull(@Email3, Email3)
			,Email4 = IsNull(@Email4, Email4)
			,AuxID1 = IsNull(@AuxID1, AuxID1)
			,AuxID2 = IsNull(@AuxID2, AuxID2)
			,AuxID3 = IsNull(@AuxID3, AuxID3)
			,AuxID4 = IsNull(@AuxID4, AuxID4)
			,AuxID5 = IsNull(@AuxID5, AuxID5)
			,AuxID6 = IsNull(@AuxID6, AuxID6)
			,AuxID7 = IsNull(@AuxID7, AuxID7)
			,AuxID8 = IsNull(@AuxID8, AuxID8)
			,AuxID9 = IsNull(@AuxID9, AuxID9)
			,AuxID10 = IsNull(@AuxID10, AuxID10)
			,pinHash = IsNull(@pinHash, pinHash)
			,Questions = IsNull(@Questions, Questions)
			,Answers = IsNull(@Answers, Answers)
			,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
			,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
			,CertResetDate = IsNull(@CertResetDate, CertResetDate)
			,CertCount = IsNull(@CertCount, CertCount)
			,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
			,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
			,MobileCount = IsNull(@MobileCount, MobileCount)
			,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
			,HardwareToken = IsNull(@HardwareToken, HardwareToken)
			,iOSDevices = IsNull(@iOSDevices, iOSDevices)
			,OATHSeed = IsNull(@OATHSeed, OATHSeed)
			,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
			,CookieKeys = IsNull(@CookieKeys, CookieKeys)
			,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
			,OTPValidation =IsNull( @OTPValidation, OTPValidation)
			,PWThrottle =IsNull( @PWThrottle, PWThrottle)
			,OIDCConsent =IsNull( @OIDCConsent, OIDCConsent)
		WHERE UserName = @UserName

		--- Update Fingerprints ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @DigitalFP)
		BEGIN
			DELETE
			FROM UserFP
			WHERE UserName = @UserName

			INSERT INTO UserFP
			SELECT @UserName
				,ObjectValue
			FROM @DigitalFP
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Push Notification ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @PNToken)
		BEGIN
			DELETE
			FROM UserPN
		WHERE UserName = @UserName

			INSERT INTO UserPN
			SELECT @UserName
				,ObjectValue
			FROM @PNToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Access History ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @AccessHistory)
		BEGIN
			DELETE
			FROM UserAccessHistory
			WHERE UserName = @UserName

			INSERT INTO UserAccessHistory
			SELECT @UserName
				,ObjectValue
			FROM @AccessHistory
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update OATH Token ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @OATHToken)
		BEGIN
			DELETE
			FROM UserOT
			WHERE UserName = @UserName

			INSERT INTO UserOT
			SELECT @UserName
				,ObjectValue
			FROM @OATHToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------
		COMMIT
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO

CREATE PROCEDURE Update User Profile (JSON)

CREATE PROC [dbo].[sp_UpdateUserProfileJson] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50) = NULL
	,@LastName VARCHAR(50) = NULL
	,@Phone1 VARCHAR(60) = NULL
	,@Phone2 VARCHAR(40) = NULL
	,@Phone3 VARCHAR(40) = NULL
	,@Phone4 VARCHAR(40) = NULL
	,@Email1 VARCHAR(60) = NULL
	,@Email2 VARCHAR(60) = NULL
	,@Email3 VARCHAR(60) = NULL
	,@Email4 VARCHAR(60) = NULL
	,@AuxID1 VARCHAR(1000) = NULL
	,@AuxID2 VARCHAR(1000) = NULL
	,@AuxID3 VARCHAR(1000) = NULL
	,@AuxID4 VARCHAR(1000) = NULL
	,@AuxID5 VARCHAR(1000) = NULL
	,@AuxID6 VARCHAR(1000) = NULL
	,@AuxID7 VARCHAR(1000) = NULL
	,@AuxID8 VARCHAR(1000) = NULL
	,@AuxID9 VARCHAR(1000) = NULL
	,@AuxID10 VARCHAR(1000) = NULL
	,@pinHash VARCHAR(120) = NULL
	,@Questions VARCHAR(1000) = NULL
	,@Answers VARCHAR(1000) = NULL
	,@ChallengeQuestion VARCHAR(1000) = NULL
	,@ChallengeAnswer VARCHAR(1000) = NULL
	,@CertResetDate VARCHAR(50) = NULL
	,@CertSerialNumber VARCHAR(1000) = NULL
	,@CertCount VARCHAR(50) = NULL
	,@MobileResetDate VARCHAR(50) = NULL
	,@MobileCount VARCHAR(50) = NULL
	,@ExtSyncPwdDate VARCHAR(50) = NULL
	,@HardwareToken VARCHAR(1000) = NULL
	,@iOSDevices VARCHAR(1000) = NULL
	,@OATHSeed VARCHAR(MAX) = NULL
	,@OneTimeOATHList VARCHAR(1000) = NULL
	,@CookieKeys VARCHAR(1000) = NULL
	,@MultiFactorThrottle VARCHAR(1000) = NULL
	,@OTPValidation VARCHAR(1000) = NULL
	,@PWThrottle VARCHAR(1000) = NULL
	,@OIDCCONSENT VARCHAR(1000) = NULL
	,@DigitalFP dbo.JsonTable READONLY
	,@PNToken dbo.JsonTable READONLY
	,@AccessHistory dbo.JsonTable READONLY
	,@OATHToken dbo.JsonTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE UserTable
		SET FirstName = IsNull(@FirstName, FirstName)
			,LastName = IsNull(@LastName, LastName)
			,Phone1 = IsNull(@Phone1, Phone1)
			,Phone2 = IsNull(@Phone2, Phone2)
			,Phone3 = IsNull(@Phone3, Phone3)
			,Phone4 = IsNull(@Phone4, Phone4)
			,Email1 = IsNull(@Email1, Email1)
			,Email2 = IsNull(@Email2, Email2)
			,Email3 = IsNull(@Email3, Email3)
			,Email4 = IsNull(@Email4, Email4)
			,AuxID1 = IsNull(@AuxID1, AuxID1)
			,AuxID2 = IsNull(@AuxID2, AuxID2)
			,AuxID3 = IsNull(@AuxID3, AuxID3)
			,AuxID4 = IsNull(@AuxID4, AuxID4)
			,AuxID5 = IsNull(@AuxID5, AuxID5)
			,AuxID6 = IsNull(@AuxID6, AuxID6)
			,AuxID7 = IsNull(@AuxID7, AuxID7)
			,AuxID8 = IsNull(@AuxID8, AuxID8)
			,AuxID9 = IsNull(@AuxID9, AuxID9)
			,AuxID10 = IsNull(@AuxID10, AuxID10)
			,pinHash = IsNull(@pinHash, pinHash)
			,Questions = IsNull(@Questions, Questions)
			,Answers = IsNull(@Answers, Answers)
			,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
			,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
			,CertResetDate = IsNull(@CertResetDate, CertResetDate)
			,CertCount = IsNull(@CertCount, CertCount)
			,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
			,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
			,MobileCount = IsNull(@MobileCount, MobileCount)
			,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
			,HardwareToken = IsNull(@HardwareToken, HardwareToken)
			,iOSDevices = IsNull(@iOSDevices, iOSDevices)
			,OATHSeed = IsNull(@OATHSeed, OATHSeed)
			,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
			,CookieKeys = IsNull(@CookieKeys, CookieKeys)
			,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
			,OTPValidation = IsNull(@OTPValidation, OTPValidation)
			,PWThrottle = IsNull(@PWThrottle, PWThrottle)
			,OIDCConsent = IsNull(@OIDCConsent, OIDCConsent)
		WHERE UserName = @UserName

		--- Update Fingerprints ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @DigitalFP)
		BEGIN
			DELETE
			FROM UserFPJson
			WHERE UserName = @UserName

			INSERT INTO UserFPJson
			SELECT @UserName
				,ObjectValue
			FROM @DigitalFP
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Push Notification ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @PNToken)
		BEGIN
			DELETE
			FROM UserPNJson
		WHERE UserName = @UserName

			INSERT INTO UserPNJson
			SELECT @UserName
				,ObjectValue
			FROM @PNToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Access History ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @AccessHistory)
		BEGIN
			DELETE
			FROM UserAccessHistoryJson
			WHERE UserName = @UserName

			INSERT INTO UserAccessHistoryJson
			SELECT @UserName
				,ObjectValue
			FROM @AccessHistory
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update OATH Token ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @OATHToken)
		BEGIN
			DELETE
			FROM UserOTJson
			WHERE UserName = @UserName

			INSERT INTO UserOTJson
			SELECT @UserName
				,ObjectValue
			FROM @OATHToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------		COMMIT
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO

Lock User

Locks an end user account.

CREATE PROCEDURE Lock User Stored Procedure

USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_LockUser]
        @UserName varchar(60)
AS
BEGIN
        DECLARE @status varchar (200)

        SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
        IF (LEN(@status) > 0)
        BEGIN
                IF charindex('LOCKED', @status) = 0
                BEGIN
                        UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
                END
        END
        ELSE
        BEGIN
                UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
        END

        SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END

Unlock User

Unlocks an end user account.

CREATE PROCEDURE Unlock User Stored Procedure

USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_UnLockUser]
        @UserName varchar(60)
AS
BEGIN
        DECLARE @status varchar (200)

        SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
        IF (LEN(@status) > 0)
        BEGIN
                IF charindex('LOCKED', @status) > 0
                BEGIN
                        SET @status = REPLACE(@status, ',LOCKED','')
                        SET @status = REPLACE(@status, 'LOCKED,','')
                        SET @status = REPLACE(@status, 'LOCKED','')
                        UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
                END
        END

        SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END

Disable User

Disables an end user account.

CREATE PROCEDURE Disable User Stored Procedure

USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DisableUser]
        @UserName varchar(60)
AS
BEGIN
        DECLARE @status varchar (200)

        SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
        IF (LEN(@status) > 0)
        BEGIN
                IF charindex('DISABLED', @status) = 0
                BEGIN
                        UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
                END
        END
        ELSE
        BEGIN
                UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
        END

        SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END

Enable User

Enables an end user account.

CREATE PROCEDURE Enable User Stored Procedure

USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_EnableUser]
        @UserName varchar(60)
AS
BEGIN
        DECLARE @status varchar (200)

        SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
        IF (LEN(@status) > 0)
        BEGIN
                IF charindex('DISABLED', @status) > 0
                BEGIN
                        SET @status = REPLACE(@status, ',DISABLED','')
                        SET @status = REPLACE(@status, 'DISABLED,','')
                        SET @status = REPLACE(@status, 'DISABLED','')
                        UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
                END
        END

        SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END

Roles and Permissions

Creates a service account user, service account role, and adds the user to the role.

Note

Change the username SecureAuthSQLUser and Password to the actual values.

CREATE Role and User

CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser; 
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount

Create Database scripts

Create Database

The CreateDatabase2404.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database.

CREATE Database script

USE [master]
GO

CREATE DATABASE [UserStore] ON PRIMARY (
	NAME = N'UserStore'
	,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore.mdf'
	,SIZE = 4096 KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024 KB
	) LOG ON (
	NAME = N'UserStore_log'
	,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore_log.ldf'
	,SIZE = 1024 KB
	,MAXSIZE = 2048 GB
	,FILEGROWTH = 10 %
	)
GO

ALTER DATABASE [UserStore]

SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [UserStore]

SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_NULLS OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_PADDING OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [UserStore]

SET ARITHABORT OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CLOSE OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET AUTO_SHRINK OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [UserStore]

SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [UserStore]

SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [UserStore]

SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [UserStore]

SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [UserStore]

SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [UserStore]

SET DISABLE_BROKER
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [UserStore]

SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [UserStore]

SET TRUSTWORTHY OFF
GO

ALTER DATABASE [UserStore]

SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [UserStore]

SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [UserStore]

SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [UserStore]

SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [UserStore]

SET READ_WRITE
GO

ALTER DATABASE [UserStore]

SET RECOVERY SIMPLE
GO

ALTER DATABASE [UserStore]

SET MULTI_USER
GO

ALTER DATABASE [UserStore]

SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [UserStore]

SET DB_CHAINING OFF
GO

USE [UserStore]
GO

CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](128) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](512) NULL,
	[AuxID2] [varchar](512) NULL,
	[AuxID3] [varchar](512) NULL,
	[AuxID4] [varchar](512) NULL,
	[AuxID5] [varchar](512) NULL,
	[AuxID6] [varchar](512) NULL,
	[AuxID7] [varchar](512) NULL,
	[AuxID8] [varchar](512) NULL,
	[AuxID9] [varchar](512) NULL,
	[AuxID10] [varchar](512) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [varchar](50) NULL,
	[CertCount] [varchar](50) NULL,
	[MobileResetDate] [varchar](50) NULL,
	[MobileCount] [varchar](50) NULL,
	[ExtSyncPwdDate] [varchar](50) NULL,
	[OATHSeed] [varchar](MAX) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL,
	[AccountStatus] [varchar](50) NULL,
	[CookieKeys] [varchar](1000) NULL,
	[UniqueUserId] [uniqueidentifier] NULL,
	[MultiFactorThrottle] [varchar](512) NULL,
	[PWThrottle] [varchar](512) NULL,
	[OTPValidation] [varchar](512) NULL,
	[OIDCConsent] [varchar](512) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO

CREATE TABLE [dbo].[UserFP] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFP (UserName)
GO

CREATE TABLE [dbo].[UserPN] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPN (UserName)
GO

CREATE TABLE [dbo].[UserAccessHistory] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
GO

CREATE TABLE [dbo].[UserOT] (
	[UserName] [varchar](60) NOT NULL
	,[OATHToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOT (UserName)
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50) = NULL
	,@LastName VARCHAR(50) = NULL
	,@Phone1 VARCHAR(60) = NULL
	,@Phone2 VARCHAR(40) = NULL
	,@Phone3 VARCHAR(40) = NULL
	,@Phone4 VARCHAR(40) = NULL
	,@Email1 VARCHAR(60) = NULL
	,@Email2 VARCHAR(60) = NULL
	,@Email3 VARCHAR(60) = NULL
	,@Email4 VARCHAR(60) = NULL
	,@AuxID1 VARCHAR(512) = NULL
	,@AuxID2 VARCHAR(512) = NULL
	,@AuxID3 VARCHAR(512) = NULL
	,@AuxID4 VARCHAR(512) = NULL
	,@AuxID5 VARCHAR(512) = NULL
	,@AuxID6 VARCHAR(512) = NULL
	,@AuxID7 VARCHAR(512) = NULL
	,@AuxID8 VARCHAR(512) = NULL
	,@AuxID9 VARCHAR(512) = NULL
	,@AuxID10 VARCHAR(512) = NULL
	,@pinHash VARCHAR(120) = NULL
	,@Questions VARCHAR(1000) = NULL
	,@Answers VARCHAR(1000) = NULL
	,@ChallengeQuestion VARCHAR(1000) = NULL
	,@ChallengeAnswer VARCHAR(1000) = NULL
	,@CertResetDate VARCHAR(50) = NULL
	,@CertSerialNumber VARCHAR(1000) = NULL
	,@CertCount VARCHAR(50) = NULL
	,@MobileResetDate VARCHAR(50) = NULL
	,@MobileCount VARCHAR(50) = NULL
	,@ExtSyncPwdDate VARCHAR(50) = NULL
	,@HardwareToken VARCHAR(1000) = NULL
	,@iOSDevices VARCHAR(1000) = NULL
	,@OATHSeed VARCHAR(MAX) = NULL
	,@OneTimeOATHList VARCHAR(1000) = NULL
	,@CookieKeys VARCHAR(1000) = NULL
	,@MultiFactorThrottle VARCHAR(512) = NULL
	,@OTPValidation VARCHAR(512) = NULL
	,@PWThrottle VARCHAR(512) = NULL
	,@OIDCConsent VARCHAR(512) = NULL
	,@DigitalFP dbo.ObjectTable READONLY
	,@PNToken dbo.ObjectTable READONLY
	,@AccessHistory dbo.ObjectTable READONLY
	,@OATHToken dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE UserTable
		SET FirstName = IsNull(@FirstName, FirstName)
			,LastName = IsNull(@LastName, LastName)
			,Phone1 = IsNull(@Phone1, Phone1)
			,Phone2 = IsNull(@Phone2, Phone2)
			,Phone3 = IsNull(@Phone3, Phone3)
			,Phone4 = IsNull(@Phone4, Phone4)
			,Email1 = IsNull(@Email1, Email1)
			,Email2 = IsNull(@Email2, Email2)
			,Email3 = IsNull(@Email3, Email3)
			,Email4 = IsNull(@Email4, Email4)
			,AuxID1 = IsNull(@AuxID1, AuxID1)
			,AuxID2 = IsNull(@AuxID2, AuxID2)
			,AuxID3 = IsNull(@AuxID3, AuxID3)
			,AuxID4 = IsNull(@AuxID4, AuxID4)
			,AuxID5 = IsNull(@AuxID5, AuxID5)
			,AuxID6 = IsNull(@AuxID6, AuxID6)
			,AuxID7 = IsNull(@AuxID7, AuxID7)
			,AuxID8 = IsNull(@AuxID8, AuxID8)
			,AuxID9 = IsNull(@AuxID9, AuxID9)
			,AuxID10 = IsNull(@AuxID10, AuxID10)
			,pinHash = IsNull(@pinHash, pinHash)
			,Questions = IsNull(@Questions, Questions)
			,Answers = IsNull(@Answers, Answers)
			,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
			,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
			,CertResetDate = IsNull(@CertResetDate, CertResetDate)
			,CertCount = IsNull(@CertCount, CertCount)
			,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
			,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
			,MobileCount = IsNull(@MobileCount, MobileCount)
			,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
			,HardwareToken = IsNull(@HardwareToken, HardwareToken)
			,iOSDevices = IsNull(@iOSDevices, iOSDevices)
			,OATHSeed = IsNull(@OATHSeed, OATHSeed)
			,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
			,CookieKeys = IsNull(@CookieKeys, CookieKeys)
			,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
			,OTPValidation =IsNull( @OTPValidation, OTPValidation)
			,PWThrottle =IsNull( @PWThrottle, PWThrottle)
			,OIDCConsent =IsNull( @OIDCConsent, OIDCConsent)
		WHERE UserName = @UserName

		--- Update Fingerprints ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @DigitalFP)
		BEGIN
			DELETE
			FROM UserFP
			WHERE UserName = @UserName

			INSERT INTO UserFP
			SELECT @UserName
				,ObjectValue
			FROM @DigitalFP
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Push Notification ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @PNToken)
		BEGIN
			DELETE
			FROM UserPN
		WHERE UserName = @UserName

			INSERT INTO UserPN
			SELECT @UserName
				,ObjectValue
			FROM @PNToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Access History ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @AccessHistory)
		BEGIN
			DELETE
			FROM UserAccessHistory
			WHERE UserName = @UserName

			INSERT INTO UserAccessHistory
			SELECT @UserName
				,ObjectValue
			FROM @AccessHistory
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update OATH Token ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @OATHToken)
		BEGIN
			DELETE
			FROM UserOT
			WHERE UserName = @UserName

			INSERT INTO UserOT
			SELECT @UserName
				,ObjectValue
			FROM @OATHToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------
		COMMIT
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [UserStore]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
	,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
    SELECT UserName
    FROM UserTable
    WHERE UserName = @UserName
    )
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
    BEGIN TRY
        BEGIN TRANSACTION
 
        UPDATE UserTable
        SET GroupList = @GroupList
        WHERE UserName = @UserName
 
        COMMIT
    END TRY
 
 
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH

GO

CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, '0') CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, '0') MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
		,IsNull(CookieKeys, '') CookieKeys
		,IsNull(GroupList, '') GroupList
		,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
		,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
		,IsNull(@OTPValidation, OTPValidation)
		,IsNull(PWThrottle, '') PWThrottle
		,IsNull(OIDCConsent, '') OIDCConsent
    FROM UserTable
    WHERE UserName = @UserName

    SELECT DigitalFP
    FROM UserFP
    WHERE UserName = @UserName

    SELECT PNToken
    FROM UserPN
    WHERE UserName = @UserName

	SELECT AccessHistory
	FROM UserAccessHistory
	WHERE UserName = @UserName

	SELECT OATHToken
	FROM UserOT
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
	SELECT [UserName]
		,ISNULL([GroupList], '')
		,ISNULL([PwdLastSet],'1/1/1900')
		,ISNULL([AccountStatus], '')
	FROM UserTable
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, NEWID()) 
IF @@ROWCOUNT > 0 
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
	UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
	IF @@ROWCOUNT > 0
	BEGIN
		SELECT 1
	END
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
    @UserName varchar(60)
AS
BEGIN
    SELECT  [Password], PasswordFormat, PasswordSalt
    FROM    dbo.UserTable
    WHERE   @UserName = UserName
END
GO

CREATE PROCEDURE [dbo].[sp_UnLockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',LOCKED','')
			SET @status = REPLACE(@status, 'LOCKED,','')
			SET @status = REPLACE(@status, 'LOCKED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_LockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_DisableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_EnableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',DISABLED','')
			SET @status = REPLACE(@status, 'DISABLED,','')
			SET @status = REPLACE(@status, 'DISABLED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser; 
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount

Create Database (JSON)

The CreateDatabaseJson2404.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database in JSON format.

CREATE Database (JSON) script

USE [master]
GO

CREATE DATABASE [UserStore] ON PRIMARY (
	NAME = N'UserStore'
	,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore.mdf'
	,SIZE = 4096 KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024 KB
	) LOG ON (
	NAME = N'UserStore_log'
	,FILENAME = N'D:\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\UserStore_log.ldf'
	,SIZE = 1024 KB
	,MAXSIZE = 2048 GB
	,FILEGROWTH = 10 %
	)
GO

ALTER DATABASE [UserStore]

SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [UserStore]

SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_NULLS OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_PADDING OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [UserStore]

SET ARITHABORT OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CLOSE OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET AUTO_SHRINK OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [UserStore]

SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [UserStore]

SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [UserStore]

SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [UserStore]

SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [UserStore]

SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [UserStore]

SET DISABLE_BROKER
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [UserStore]

SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [UserStore]

SET TRUSTWORTHY OFF
GO

ALTER DATABASE [UserStore]

SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [UserStore]

SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [UserStore]

SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [UserStore]

SET HONOR_BROKER_PRIORITY OFF
GO

ALTER DATABASE [UserStore]

SET READ_WRITE
GO

ALTER DATABASE [UserStore]

SET RECOVERY SIMPLE
GO

ALTER DATABASE [UserStore]

SET MULTI_USER
GO

ALTER DATABASE [UserStore]

SET PAGE_VERIFY CHECKSUM
GO

ALTER DATABASE [UserStore]

SET DB_CHAINING OFF
GO

USE [UserStore]
GO

CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO

CREATE TYPE [dbo].[JsonTable] AS TABLE([ObjectValue] [varchar](max) NULL)
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](128) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](1000) NULL,
	[AuxID2] [varchar](1000) NULL,
	[AuxID3] [varchar](1000) NULL,
	[AuxID4] [varchar](1000) NULL,
	[AuxID5] [varchar](1000) NULL,
	[AuxID6] [varchar](1000) NULL,
	[AuxID7] [varchar](1000) NULL,
	[AuxID8] [varchar](1000) NULL,
	[AuxID9] [varchar](1000) NULL,
	[AuxID10] [varchar](1000) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [varchar](50) NULL,
	[CertCount] [varchar](50) NULL,
	[MobileResetDate] [varchar](50) NULL,
	[MobileCount] [varchar](50) NULL,
	[ExtSyncPwdDate] [varchar](50) NULL,
	[OATHSeed] [varchar](MAX) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[CookieKeys] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL,
	[AccountStatus] [varchar](50) NULL,
	[UniqueUserId] [uniqueidentifier] NULL,
	[MultiFactorThrottle] [varchar](1000) NULL,
	[OTPValidation] [varchar](1000) NULL,
	[PWThrottle] [varchar](1000) NULL,
	[OIDCConsent] [varchar](1000) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO

CREATE TABLE [dbo].[UserFPJson] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFPJson (UserName)
GO

CREATE TABLE [dbo].[UserPNJson] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPNJson (UserName)
GO

CREATE TABLE [dbo].[UserAccessHistoryJson] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistoryJson (UserName)
GO

CREATE TABLE [dbo].[UserOTJson] (
	[UserName] [varchar](60) NOT NULL
	,[OATHToken] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOTJson (UserName)
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_UpdateUserProfileJson] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50) = NULL
	,@LastName VARCHAR(50) = NULL
	,@Phone1 VARCHAR(60) = NULL
	,@Phone2 VARCHAR(40) = NULL
	,@Phone3 VARCHAR(40) = NULL
	,@Phone4 VARCHAR(40) = NULL
	,@Email1 VARCHAR(60) = NULL
	,@Email2 VARCHAR(60) = NULL
	,@Email3 VARCHAR(60) = NULL
	,@Email4 VARCHAR(60) = NULL
	,@AuxID1 VARCHAR(1000) = NULL
	,@AuxID2 VARCHAR(1000) = NULL
	,@AuxID3 VARCHAR(1000) = NULL
	,@AuxID4 VARCHAR(1000) = NULL
	,@AuxID5 VARCHAR(1000) = NULL
	,@AuxID6 VARCHAR(1000) = NULL
	,@AuxID7 VARCHAR(1000) = NULL
	,@AuxID8 VARCHAR(1000) = NULL
	,@AuxID9 VARCHAR(1000) = NULL
	,@AuxID10 VARCHAR(1000) = NULL
	,@pinHash VARCHAR(120) = NULL
	,@Questions VARCHAR(1000) = NULL
	,@Answers VARCHAR(1000) = NULL
	,@ChallengeQuestion VARCHAR(1000) = NULL
	,@ChallengeAnswer VARCHAR(1000) = NULL
	,@CertResetDate VARCHAR(50) = NULL
	,@CertSerialNumber VARCHAR(1000) = NULL
	,@CertCount VARCHAR(50) = NULL
	,@MobileResetDate VARCHAR(50) = NULL
	,@MobileCount VARCHAR(50) = NULL
	,@ExtSyncPwdDate VARCHAR(50) = NULL
	,@HardwareToken VARCHAR(1000) = NULL
	,@iOSDevices VARCHAR(1000) = NULL
	,@OATHSeed VARCHAR(MAX) = NULL
	,@OneTimeOATHList VARCHAR(1000) = NULL
	,@CookieKeys VARCHAR(1000) = NULL
	,@MultiFactorThrottle VARCHAR(1000) = NULL
	,@OTPValidation VARCHAR(1000) = NULL
	,@PWThrottle VARCHAR(1000) = NULL
	,@OIDCCONSENT VARCHAR(1000) = NULL
	,@DigitalFP dbo.JsonTable READONLY
	,@PNToken dbo.JsonTable READONLY
	,@AccessHistory dbo.JsonTable READONLY
	,@OATHToken dbo.JsonTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE UserTable
		SET FirstName = IsNull(@FirstName, FirstName)
			,LastName = IsNull(@LastName, LastName)
			,Phone1 = IsNull(@Phone1, Phone1)
			,Phone2 = IsNull(@Phone2, Phone2)
			,Phone3 = IsNull(@Phone3, Phone3)
			,Phone4 = IsNull(@Phone4, Phone4)
			,Email1 = IsNull(@Email1, Email1)
			,Email2 = IsNull(@Email2, Email2)
			,Email3 = IsNull(@Email3, Email3)
			,Email4 = IsNull(@Email4, Email4)
			,AuxID1 = IsNull(@AuxID1, AuxID1)
			,AuxID2 = IsNull(@AuxID2, AuxID2)
			,AuxID3 = IsNull(@AuxID3, AuxID3)
			,AuxID4 = IsNull(@AuxID4, AuxID4)
			,AuxID5 = IsNull(@AuxID5, AuxID5)
			,AuxID6 = IsNull(@AuxID6, AuxID6)
			,AuxID7 = IsNull(@AuxID7, AuxID7)
			,AuxID8 = IsNull(@AuxID8, AuxID8)
			,AuxID9 = IsNull(@AuxID9, AuxID9)
			,AuxID10 = IsNull(@AuxID10, AuxID10)
			,pinHash = IsNull(@pinHash, pinHash)
			,Questions = IsNull(@Questions, Questions)
			,Answers = IsNull(@Answers, Answers)
			,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
			,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
			,CertResetDate = IsNull(@CertResetDate, CertResetDate)
			,CertCount = IsNull(@CertCount, CertCount)
			,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
			,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
			,MobileCount = IsNull(@MobileCount, MobileCount)
			,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
			,HardwareToken = IsNull(@HardwareToken, HardwareToken)
			,iOSDevices = IsNull(@iOSDevices, iOSDevices)
			,OATHSeed = IsNull(@OATHSeed, OATHSeed)
			,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
			,CookieKeys = IsNull(@CookieKeys, CookieKeys)
			,MultiFactorThrottle = IsNull(@MultiFactorThrottle, MultiFactorThrottle)
			,OTPValidation = IsNull(@OTPValidation, OTPValidation)
			,PWThrottle = IsNull(@PWThrottle, PWThrottle)
			,OIDCConsent = IsNull(@OIDCConsent, OIDCConsent)
		WHERE UserName = @UserName

		--- Update Fingerprints ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @DigitalFP)
		BEGIN
			DELETE
			FROM UserFPJson
			WHERE UserName = @UserName

			INSERT INTO UserFPJson
			SELECT @UserName
				,ObjectValue
			FROM @DigitalFP
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Push Notification ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @PNToken)
		BEGIN
			DELETE
			FROM UserPNJson
		WHERE UserName = @UserName

			INSERT INTO UserPNJson
			SELECT @UserName
				,ObjectValue
			FROM @PNToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Access History ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @AccessHistory)
		BEGIN
			DELETE
			FROM UserAccessHistoryJson
			WHERE UserName = @UserName

			INSERT INTO UserAccessHistoryJson
			SELECT @UserName
				,ObjectValue
			FROM @AccessHistory
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update OATH Token ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @OATHToken)
		BEGIN
			DELETE
			FROM UserOTJson
			WHERE UserName = @UserName

			INSERT INTO UserOTJson
			SELECT @UserName
				,ObjectValue
			FROM @OATHToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------		COMMIT
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
    ,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
    SELECT UserName
    FROM UserTable
    WHERE UserName = @UserName
    )
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
    BEGIN TRY
        BEGIN TRANSACTION
  
        UPDATE UserTable
        SET GroupList = @GroupList
        WHERE UserName = @UserName
  
        COMMIT
    END TRY
  
  
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH
GO
CREATE PROC [dbo].[sp_GetUserProfileJson] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, '0') CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, '0') MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
        ,isNull(CookieKeys, '') CookieKeys
        ,IsNull(GroupList, '') GroupList
		,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
		,IsNull(MultiFactorThrottle, '') MultiFactorThrottle
		,IsNull(OTPValidation, '') OTPValidation
		,IsNull(PWThrottle, '') PWThrottle
		,IsNull(OIDCConsent, '') OIDCConsent
	FROM UserTable
    WHERE UserName = @UserName

    SELECT DigitalFP
    FROM UserFPJson
    WHERE UserName = @UserName

    SELECT PNToken
    FROM UserPNJson
    WHERE UserName = @UserName

	SELECT AccessHistory
	FROM UserAccessHistoryJson
	WHERE UserName = @UserName

	SELECT OATHToken
	FROM UserOTJson
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
	SELECT [UserName]
		,ISNULL([GroupList], '')
		,ISNULL([PwdLastSet],'1/1/1900')
		,ISNULL([AccountStatus], '')
	FROM UserTable
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, NEWID()) 
IF @@ROWCOUNT > 0 
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
	UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
	IF @@ROWCOUNT > 0
	BEGIN
		SELECT 1
	END
END

GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
    @UserName varchar(60)
AS
BEGIN
    SELECT  [Password], PasswordFormat, PasswordSalt
    FROM    dbo.UserTable
    WHERE   @UserName = UserName
END
GO

CREATE PROCEDURE [dbo].[sp_UnLockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',LOCKED','')
			SET @status = REPLACE(@status, 'LOCKED,','')
			SET @status = REPLACE(@status, 'LOCKED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_LockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_DisableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_EnableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',DISABLED','')
			SET @status = REPLACE(@status, 'DISABLED,','')
			SET @status = REPLACE(@status, 'DISABLED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.JsonTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount

Create Database scripts – Amazon RDS for SQL Server in AWS

There are some slight modifications to the Create Database scripts to support Amazon RDS for SQL Server in AWS. See the following database scripts with comments specifying the location of where the database must be created.

Create Database - Amazon RDS for SQL

The CreateDatabaseRDSAWS.sql script creates a "UserStore" database and also creates all the above tables, objects, and stored procedures within that database. See the comments in the script specifying the location of where to create the database.

CREATE Database script

USE [master]
GO

CREATE DATABASE [UserStore] ON PRIMARY (
	NAME = N'UserStore'
	,FILENAME = N'D:\rdsdbdata\DATA\UserStore.mdf' --please change it for the correct path
	,SIZE = 4096 KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024 KB
	) LOG ON (
	NAME = N'UserStore_log'
	,FILENAME = N'D:\rdsdbdata\DATA\UserStore_log.ldf' --please change it for the correct path
	,SIZE = 1024 KB
	,MAXSIZE = 2048 GB
	,FILEGROWTH = 10 %
	)
GO

ALTER DATABASE [UserStore]

SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [UserStore]

SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_NULLS OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_PADDING OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [UserStore]

SET ARITHABORT OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CLOSE OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET AUTO_SHRINK OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [UserStore]

SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [UserStore]

SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [UserStore]

SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [UserStore]

SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [UserStore]

SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [UserStore]

SET DISABLE_BROKER
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [UserStore]

SET DATE_CORRELATION_OPTIMIZATION OFF
GO


ALTER DATABASE [UserStore]

SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [UserStore]

SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [UserStore]

SET READ_COMMITTED_SNAPSHOT OFF
GO


ALTER DATABASE [UserStore]

SET READ_WRITE
GO

ALTER DATABASE [UserStore]

SET RECOVERY SIMPLE
GO

ALTER DATABASE [UserStore]

SET MULTI_USER
GO

ALTER DATABASE [UserStore]

SET PAGE_VERIFY CHECKSUM
GO

USE [UserStore]
GO

CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](128) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](512) NULL,
	[AuxID2] [varchar](512) NULL,
	[AuxID3] [varchar](512) NULL,
	[AuxID4] [varchar](512) NULL,
	[AuxID5] [varchar](512) NULL,
	[AuxID6] [varchar](512) NULL,
	[AuxID7] [varchar](512) NULL,
	[AuxID8] [varchar](512) NULL,
	[AuxID9] [varchar](512) NULL,
	[AuxID10] [varchar](512) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [varchar](50) NULL,
	[CertCount] [varchar](50) NULL,
	[MobileResetDate] [varchar](50) NULL,
	[MobileCount] [varchar](50) NULL,
	[ExtSyncPwdDate] [varchar](50) NULL,
	[OATHSeed] [varchar](MAX) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL,
	[AccountStatus] [varchar](50) NULL,
	[CookieKeys] [varchar](1000) NULL,
	[UniqueUserId] [uniqueidentifier] NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX UserIndex ON UserStore.dbo.UserTable (UserName)
GO

CREATE TABLE [dbo].[UserFP] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX FPIndex ON UserStore.dbo.UserFP (UserName)
GO

CREATE TABLE [dbo].[UserPN] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX PNIndex ON UserStore.dbo.UserPN (UserName)
GO

CREATE TABLE [dbo].[UserAccessHistory] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX AccessIndex ON UserStore.dbo.UserAccessHistory (UserName)
GO

CREATE TABLE [dbo].[UserOT] (
	[UserName] [varchar](60) NOT NULL
	,[OATHToken] [varbinary](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX OTIndex ON UserStore.dbo.UserOT (UserName)
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_UpdateUserProfile] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50) = NULL
	,@LastName VARCHAR(50) = NULL
	,@Phone1 VARCHAR(60) = NULL
	,@Phone2 VARCHAR(40) = NULL
	,@Phone3 VARCHAR(40) = NULL
	,@Phone4 VARCHAR(40) = NULL
	,@Email1 VARCHAR(60) = NULL
	,@Email2 VARCHAR(60) = NULL
	,@Email3 VARCHAR(60) = NULL
	,@Email4 VARCHAR(60) = NULL
	,@AuxID1 VARCHAR(512) = NULL
	,@AuxID2 VARCHAR(512) = NULL
	,@AuxID3 VARCHAR(512) = NULL
	,@AuxID4 VARCHAR(512) = NULL
	,@AuxID5 VARCHAR(512) = NULL
	,@AuxID6 VARCHAR(512) = NULL
	,@AuxID7 VARCHAR(512) = NULL
	,@AuxID8 VARCHAR(512) = NULL
	,@AuxID9 VARCHAR(512) = NULL
	,@AuxID10 VARCHAR(512) = NULL
	,@pinHash VARCHAR(120) = NULL
	,@Questions VARCHAR(1000) = NULL
	,@Answers VARCHAR(1000) = NULL
	,@ChallengeQuestion VARCHAR(1000) = NULL
	,@ChallengeAnswer VARCHAR(1000) = NULL
	,@CertResetDate VARCHAR(50) = NULL
	,@CertSerialNumber VARCHAR(1000) = NULL
	,@CertCount VARCHAR(50) = NULL
	,@MobileResetDate VARCHAR(50) = NULL
	,@MobileCount VARCHAR(50) = NULL
	,@ExtSyncPwdDate VARCHAR(50) = NULL
	,@HardwareToken VARCHAR(1000) = NULL
	,@iOSDevices VARCHAR(1000) = NULL
	,@OATHSeed VARCHAR(MAX) = NULL
	,@OneTimeOATHList VARCHAR(1000) = NULL
	,@CookieKeys VARCHAR(1000) = NULL
	,@DigitalFP dbo.ObjectTable READONLY
	,@PNToken dbo.ObjectTable READONLY
	,@AccessHistory dbo.ObjectTable READONLY
	,@OATHToken dbo.ObjectTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE UserTable
		SET FirstName = IsNull(@FirstName, FirstName)
			,LastName = IsNull(@LastName, LastName)
			,Phone1 = IsNull(@Phone1, Phone1)
			,Phone2 = IsNull(@Phone2, Phone2)
			,Phone3 = IsNull(@Phone3, Phone3)
			,Phone4 = IsNull(@Phone4, Phone4)
			,Email1 = IsNull(@Email1, Email1)
			,Email2 = IsNull(@Email2, Email2)
			,Email3 = IsNull(@Email3, Email3)
			,Email4 = IsNull(@Email4, Email4)
			,AuxID1 = IsNull(@AuxID1, AuxID1)
			,AuxID2 = IsNull(@AuxID2, AuxID2)
			,AuxID3 = IsNull(@AuxID3, AuxID3)
			,AuxID4 = IsNull(@AuxID4, AuxID4)
			,AuxID5 = IsNull(@AuxID5, AuxID5)
			,AuxID6 = IsNull(@AuxID6, AuxID6)
			,AuxID7 = IsNull(@AuxID7, AuxID7)
			,AuxID8 = IsNull(@AuxID8, AuxID8)
			,AuxID9 = IsNull(@AuxID9, AuxID9)
			,AuxID10 = IsNull(@AuxID10, AuxID10)
			,pinHash = IsNull(@pinHash, pinHash)
			,Questions = IsNull(@Questions, Questions)
			,Answers = IsNull(@Answers, Answers)
			,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
			,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
			,CertResetDate = IsNull(@CertResetDate, CertResetDate)
			,CertCount = IsNull(@CertCount, CertCount)
			,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
			,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
			,MobileCount = IsNull(@MobileCount, MobileCount)
			,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
			,HardwareToken = IsNull(@HardwareToken, HardwareToken)
			,iOSDevices = IsNull(@iOSDevices, iOSDevices)
			,OATHSeed = IsNull(@OATHSeed, OATHSeed)
			,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
			,CookieKeys = IsNull(@CookieKeys, CookieKeys)
		WHERE UserName = @UserName

		--- Update Fingerprints ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @DigitalFP)
		BEGIN
			DELETE
			FROM UserFP
			WHERE UserName = @UserName

			INSERT INTO UserFP
			SELECT @UserName
				,ObjectValue
			FROM @DigitalFP
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Push Notification ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @PNToken)
		BEGIN
			DELETE
			FROM UserPN
		WHERE UserName = @UserName

			INSERT INTO UserPN
			SELECT @UserName
				,ObjectValue
			FROM @PNToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Access History ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @AccessHistory)
		BEGIN
			DELETE
			FROM UserAccessHistory
			WHERE UserName = @UserName

			INSERT INTO UserAccessHistory
			SELECT @UserName
				,ObjectValue
			FROM @AccessHistory
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update OATH Token ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @OATHToken)
		BEGIN
			DELETE
			FROM UserOT
			WHERE UserName = @UserName

			INSERT INTO UserOT
			SELECT @UserName
				,ObjectValue
			FROM @OATHToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------
		COMMIT
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

USE [UserStore]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
	,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
    SELECT UserName
    FROM UserTable
    WHERE UserName = @UserName
    )
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
    BEGIN TRY
        BEGIN TRANSACTION
 
        UPDATE UserTable
        SET GroupList = @GroupList
        WHERE UserName = @UserName
 
        COMMIT
    END TRY
 
 
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH

GO

CREATE PROC [dbo].[sp_GetUserProfile] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, '0') CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, '0') MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
		,IsNull(CookieKeys, '') CookieKeys
		,IsNull(GroupList, '') GroupList
		,IsNull(UniqueUserId, '00000000-0000-0000-0000-000000000000') UniqueUserId
    FROM UserTable
    WHERE UserName = @UserName

    SELECT DigitalFP
    FROM UserFP
    WHERE UserName = @UserName

    SELECT PNToken
    FROM UserPN
    WHERE UserName = @UserName

	SELECT AccessHistory
	FROM UserAccessHistory
	WHERE UserName = @UserName

	SELECT OATHToken
	FROM UserOT
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
	SELECT [UserName]
		,ISNULL([GroupList], '')
		,ISNULL([PwdLastSet],'1/1/1900')
		,ISNULL([AccountStatus], '')
	FROM UserTable
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [UniqueUserId]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, NEWID()) 
IF @@ROWCOUNT > 0 
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
	UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
	IF @@ROWCOUNT > 0
	BEGIN
		SELECT 1
	END
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
    @UserName varchar(60)
AS
BEGIN
    SELECT  [Password], PasswordFormat, PasswordSalt
    FROM    dbo.UserTable
    WHERE   @UserName = UserName
END
GO

CREATE PROCEDURE [dbo].[sp_UnLockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',LOCKED','')
			SET @status = REPLACE(@status, 'LOCKED,','')
			SET @status = REPLACE(@status, 'LOCKED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_LockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_DisableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_EnableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',DISABLED','')
			SET @status = REPLACE(@status, 'DISABLED,','')
			SET @status = REPLACE(@status, 'DISABLED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser; 
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfile TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfile TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount

Create Database (JSON) - Amazon RDS for SQL

The CreateDatabaseRDSAWSJson.sql script creates a "UserStore" database and also creates all the above tables, objects, and stored procedures within that database in JSON format. See the comments in the script specifying the location of where to create the database.

CREATE Database (JSON) script

USE [master]
GO

CREATE DATABASE [UserStore] ON PRIMARY (
	NAME = N'UserStore'
	,FILENAME = N'D:\rdsdbdata\DATA\UserStore.mdf' --please change it for the correct path
	,SIZE = 4096 KB
	,MAXSIZE = UNLIMITED
	,FILEGROWTH = 1024 KB
	) LOG ON (
	NAME = N'UserStore_log'
	,FILENAME = N'D:\rdsdbdata\DATA\UserStore_log.ldf' --please change it for the correct path
	,SIZE = 1024 KB
	,MAXSIZE = 2048 GB
	,FILEGROWTH = 10 %
	)
GO

ALTER DATABASE [UserStore]

SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
	EXEC [UserStore].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [UserStore]

SET ANSI_NULL_DEFAULT OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_NULLS OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_PADDING OFF
GO

ALTER DATABASE [UserStore]

SET ANSI_WARNINGS OFF
GO

ALTER DATABASE [UserStore]

SET ARITHABORT OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CLOSE OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_CREATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET AUTO_SHRINK OFF
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS ON
GO

ALTER DATABASE [UserStore]

SET CURSOR_CLOSE_ON_COMMIT OFF
GO

ALTER DATABASE [UserStore]

SET CURSOR_DEFAULT GLOBAL
GO

ALTER DATABASE [UserStore]

SET CONCAT_NULL_YIELDS_NULL OFF
GO

ALTER DATABASE [UserStore]

SET NUMERIC_ROUNDABORT OFF
GO

ALTER DATABASE [UserStore]

SET QUOTED_IDENTIFIER OFF
GO

ALTER DATABASE [UserStore]

SET RECURSIVE_TRIGGERS OFF
GO

ALTER DATABASE [UserStore]

SET DISABLE_BROKER
GO

ALTER DATABASE [UserStore]

SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO

ALTER DATABASE [UserStore]

SET DATE_CORRELATION_OPTIMIZATION OFF
GO

ALTER DATABASE [UserStore]

SET ALLOW_SNAPSHOT_ISOLATION OFF
GO

ALTER DATABASE [UserStore]

SET PARAMETERIZATION SIMPLE
GO

ALTER DATABASE [UserStore]

SET READ_COMMITTED_SNAPSHOT OFF
GO

ALTER DATABASE [UserStore]

SET READ_WRITE
GO

ALTER DATABASE [UserStore]

SET RECOVERY SIMPLE
GO

ALTER DATABASE [UserStore]

SET MULTI_USER
GO

ALTER DATABASE [UserStore]

SET PAGE_VERIFY CHECKSUM
GO

USE [UserStore]
GO

CREATE TYPE [dbo].[ObjectTable] AS TABLE ([ObjectValue] [varbinary](max) NULL)
GO

CREATE TYPE [dbo].[JsonTable] AS TABLE([ObjectValue] [varchar](max) NULL)
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[UserTable](
	[UserName] [varchar](60) NOT NULL,
	[Password] [varchar](128) NULL,
	[PasswordSalt] [varchar](128) NULL,
	[PasswordFormat] [int] NULL,
	[PwdLastSet] [datetime] NULL,
	[FirstName] [varchar](50) NULL,
	[LastName] [varchar](50) NULL,
	[Phone1] [varchar](40) NULL,
	[Phone2] [varchar](40) NULL,
	[Phone3] [varchar](40) NULL,
	[Phone4] [varchar](40) NULL,
	[Email1] [varchar](60) NULL,
	[Email2] [varchar](60) NULL,
	[Email3] [varchar](60) NULL,
	[Email4] [varchar](60) NULL,
	[AuxID1] [varchar](512) NULL,
	[AuxID2] [varchar](512) NULL,
	[AuxID3] [varchar](512) NULL,
	[AuxID4] [varchar](512) NULL,
	[AuxID5] [varchar](512) NULL,
	[AuxID6] [varchar](512) NULL,
	[AuxID7] [varchar](512) NULL,
	[AuxID8] [varchar](512) NULL,
	[AuxID9] [varchar](512) NULL,
	[AuxID10] [varchar](512) NULL,
	[pinHash] [varchar](120) NULL,
	[Questions] [varchar](1000) NULL,
	[Answers] [varchar](1000) NULL,
	[ChallengeQuestion] [varchar](1000) NULL,
	[ChallengeAnswer] [varchar](1000) NULL,
	[CertResetDate] [varchar](50) NULL,
	[CertCount] [varchar](50) NULL,
	[MobileResetDate] [varchar](50) NULL,
	[MobileCount] [varchar](50) NULL,
	[ExtSyncPwdDate] [varchar](50) NULL,
	[OATHSeed] [varchar](MAX) NULL,
	[OneTimeOATHList] [varchar](1000) NULL,
	[CookieKeys] [varchar](1000) NULL,
	[iOSDevices] [varchar](50) NULL,
	[HardwareToken] [varchar](50) NULL,
	[CertSerialNumber] [varchar](1000) NULL,
	[GroupList] [varchar](1000) NULL,
	[AccountStatus] [varchar](50) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[UserFPJson] (
	[UserName] [varchar](60) NOT NULL
	,[DigitalFP] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserPNJson] (
	[UserName] [varchar](60) NOT NULL
	,[PNToken] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserAccessHistoryJson] (
	[UserName] [varchar](60) NOT NULL
	,[AccessHistory] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserOTJson] (
	[UserName] [varchar](60) NOT NULL
	,[OATHToken] [varchar](max) NOT NULL
	) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_UpdateUserProfileJson] @UserName VARCHAR(60)
	,@FirstName VARCHAR(50) = NULL
	,@LastName VARCHAR(50) = NULL
	,@Phone1 VARCHAR(60) = NULL
	,@Phone2 VARCHAR(40) = NULL
	,@Phone3 VARCHAR(40) = NULL
	,@Phone4 VARCHAR(40) = NULL
	,@Email1 VARCHAR(60) = NULL
	,@Email2 VARCHAR(60) = NULL
	,@Email3 VARCHAR(60) = NULL
	,@Email4 VARCHAR(60) = NULL
	,@AuxID1 VARCHAR(512) = NULL
	,@AuxID2 VARCHAR(512) = NULL
	,@AuxID3 VARCHAR(512) = NULL
	,@AuxID4 VARCHAR(512) = NULL
	,@AuxID5 VARCHAR(512) = NULL
	,@AuxID6 VARCHAR(512) = NULL
	,@AuxID7 VARCHAR(512) = NULL
	,@AuxID8 VARCHAR(512) = NULL
	,@AuxID9 VARCHAR(512) = NULL
	,@AuxID10 VARCHAR(512) = NULL
	,@pinHash VARCHAR(120) = NULL
	,@Questions VARCHAR(1000) = NULL
	,@Answers VARCHAR(1000) = NULL
	,@ChallengeQuestion VARCHAR(1000) = NULL
	,@ChallengeAnswer VARCHAR(1000) = NULL
	,@CertResetDate VARCHAR(50) = NULL
	,@CertSerialNumber VARCHAR(1000) = NULL
	,@CertCount VARCHAR(50) = NULL
	,@MobileResetDate VARCHAR(50) = NULL
	,@MobileCount VARCHAR(50) = NULL
	,@ExtSyncPwdDate VARCHAR(50) = NULL
	,@HardwareToken VARCHAR(1000) = NULL
	,@iOSDevices VARCHAR(1000) = NULL
	,@OATHSeed VARCHAR(MAX) = NULL
	,@OneTimeOATHList VARCHAR(1000) = NULL
	,@CookieKeys VARCHAR(1000) = NULL
	,@DigitalFP dbo.JsonTable READONLY
	,@PNToken dbo.JsonTable READONLY
	,@AccessHistory dbo.JsonTable READONLY
	,@OATHToken dbo.JsonTable READONLY
AS
IF NOT EXISTS (
		SELECT UserName
		FROM UserTable
		WHERE UserName = @UserName
		)
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
	BEGIN TRY
		BEGIN TRANSACTION

		UPDATE UserTable
		SET FirstName = IsNull(@FirstName, FirstName)
			,LastName = IsNull(@LastName, LastName)
			,Phone1 = IsNull(@Phone1, Phone1)
			,Phone2 = IsNull(@Phone2, Phone2)
			,Phone3 = IsNull(@Phone3, Phone3)
			,Phone4 = IsNull(@Phone4, Phone4)
			,Email1 = IsNull(@Email1, Email1)
			,Email2 = IsNull(@Email2, Email2)
			,Email3 = IsNull(@Email3, Email3)
			,Email4 = IsNull(@Email4, Email4)
			,AuxID1 = IsNull(@AuxID1, AuxID1)
			,AuxID2 = IsNull(@AuxID2, AuxID2)
			,AuxID3 = IsNull(@AuxID3, AuxID3)
			,AuxID4 = IsNull(@AuxID4, AuxID4)
			,AuxID5 = IsNull(@AuxID5, AuxID5)
			,AuxID6 = IsNull(@AuxID6, AuxID6)
			,AuxID7 = IsNull(@AuxID7, AuxID7)
			,AuxID8 = IsNull(@AuxID8, AuxID8)
			,AuxID9 = IsNull(@AuxID9, AuxID9)
			,AuxID10 = IsNull(@AuxID10, AuxID10)
			,pinHash = IsNull(@pinHash, pinHash)
			,Questions = IsNull(@Questions, Questions)
			,Answers = IsNull(@Answers, Answers)
			,ChallengeQuestion = IsNull(@ChallengeQuestion, ChallengeQuestion)
			,ChallengeAnswer = IsNull(@ChallengeAnswer, ChallengeAnswer)
			,CertResetDate = IsNull(@CertResetDate, CertResetDate)
			,CertCount = IsNull(@CertCount, CertCount)
			,CertSerialNumber = IsNull(@CertSerialNumber, CertSerialNumber)
			,MobileResetDate = IsNull(@MobileResetDate, MobileResetDate)
			,MobileCount = IsNull(@MobileCount, MobileCount)
			,ExtSyncPwdDate = IsNull(@ExtSyncPwdDate, ExtSyncPwdDate)
			,HardwareToken = IsNull(@HardwareToken, HardwareToken)
			,iOSDevices = IsNull(@iOSDevices, iOSDevices)
			,OATHSeed = IsNull(@OATHSeed, OATHSeed)
			,OneTimeOATHList = IsNull(@OneTimeOATHList, OneTimeOATHList)
			,CookieKeys = IsNull(@CookieKeys, CookieKeys)
		WHERE UserName = @UserName

		--- Update Fingerprints ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @DigitalFP)
		BEGIN
			DELETE
			FROM UserFPJson
			WHERE UserName = @UserName

			INSERT INTO UserFPJson
			SELECT @UserName
				,ObjectValue
			FROM @DigitalFP
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Push Notification ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @PNToken)
		BEGIN
			DELETE
			FROM UserPNJson
		WHERE UserName = @UserName

			INSERT INTO UserPNJson
			SELECT @UserName
				,ObjectValue
			FROM @PNToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update Access History ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @AccessHistory)
		BEGIN
			DELETE
			FROM UserAccessHistoryJson
			WHERE UserName = @UserName

			INSERT INTO UserAccessHistoryJson
			SELECT @UserName
				,ObjectValue
			FROM @AccessHistory
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------

		--- Update OATH Token ---
		IF EXISTS (SELECT 
			ObjectValue
		FROM @OATHToken)
		BEGIN
			DELETE
			FROM UserOTJson
			WHERE UserName = @UserName

			INSERT INTO UserOTJson
			SELECT @UserName
				,ObjectValue
			FROM @OATHToken
			WHERE ObjectValue <> 'ERASE'
		END
		---------------------------		COMMIT
	END TRY

	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK
	END CATCH
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
USE [UserStore]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[sp_AddUserToGroup] @UserName VARCHAR(60)
    ,@GroupList VARCHAR(1000) = ''
AS
IF NOT EXISTS (
    SELECT UserName
    FROM UserTable
    WHERE UserName = @UserName
    )
BEGIN
 INSERT INTO UserTable (UserName) VALUES (@UserName)
END
    BEGIN TRY
        BEGIN TRANSACTION
  
        UPDATE UserTable
        SET GroupList = @GroupList
        WHERE UserName = @UserName
  
        COMMIT
    END TRY
  
  
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH
GO
CREATE PROC [dbo].[sp_GetUserProfileJson] @UserName VARCHAR(60)
AS
BEGIN
    SELECT UserName
        ,IsNull(FirstName, '') FirstName
        ,IsNull(LastName, '') LastName
        ,IsNull(Phone1, '') Phone1
        ,IsNull(Phone2, '') Phone2
        ,IsNull(Phone3, '') Phone3
        ,IsNull(Phone4, '') Phone4
        ,IsNull(Email1, '') Email1
        ,IsNull(Email2, '') Email2
        ,IsNull(Email3, '') Email3
        ,IsNull(Email4, '') Email4
        ,IsNull(AuxID1, '') AuxID1
        ,IsNull(AuxID2, '') AuxID2
        ,IsNull(AuxID3, '') AuxID3
        ,IsNull(AuxID4, '') AuxID4
        ,IsNull(AuxID5, '') AuxID5
        ,IsNull(AuxID6, '') AuxID6
        ,IsNull(AuxID7, '') AuxID7
        ,IsNull(AuxID8, '') AuxID8
        ,IsNull(AuxID9, '') AuxID9
        ,IsNull(AuxID10, '') AuxID10
        ,IsNull(pinHash, '') pinHash
        ,IsNull(Questions, '') Questions
        ,IsNull(Answers, '') Answers
        ,IsNull(ChallengeQuestion, '') ChallengeQuestion
        ,IsNull(ChallengeAnswer, '') ChallengeAnswer
        ,IsNull(CertResetDate, '1/1/1900') CertResetDate
        ,IsNull(CertCount, '0') CertCount
        ,IsNull(CertSerialNumber, '') CertSerialNumber
        ,IsNull(MobileResetDate, '1/1/1900') MobileResetDate
        ,IsNull(MobileCount, '0') MobileCount
        ,IsNull(ExtSyncPwdDate, '1/1/1900') ExtSyncPwdDate
        ,IsNull(HardwareToken, '') HardwareToken
        ,IsNull(iOSDevices, '') iOSDevices
        ,IsNull(OATHSeed, '') OATHSeed
        ,IsNull(OneTimeOATHList, '') OneTimeOATHList
        ,isNull(CookieKeys, '') CookieKeys
        ,IsNull(GroupList, '') GroupList
    FROM UserTable
    WHERE UserName = @UserName

    SELECT DigitalFP
    FROM UserFPJson
    WHERE UserName = @UserName

    SELECT PNToken
    FROM UserPNJson
    WHERE UserName = @UserName

	SELECT AccessHistory
	FROM UserAccessHistoryJson
	WHERE UserName = @UserName

	SELECT OATHToken
	FROM UserOTJson
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_GetUser] @UserName VARCHAR(60)
AS
BEGIN
	SELECT [UserName]
		,ISNULL([GroupList], '')
		,ISNULL([PwdLastSet],'1/1/1900')
		,ISNULL([AccountStatus], '')
	FROM UserTable
	WHERE UserName = @UserName
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROC [dbo].[sp_CreateUser]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int,
@Status int OUTPUT
AS
BEGIN
IF NOT EXISTS (SELECT 1 FROM UserTable WHERE UserName = @UserName)
INSERT INTO UserTable ([UserName], [Password], [PasswordSalt], [PasswordFormat], [PwdLastSet]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat, GetDate())
 
IF @@ROWCOUNT > 0 
SELECT @Status = 0
ELSE
SELECT @Status = 1
END
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[sp_ResetPassword]
@UserName VARCHAR(60),
@Password VARCHAR(128),
@PasswordSalt VARCHAR(128),
@PasswordFormat int
AS
BEGIN
	UPDATE UserTable SET [Password]=@Password, [PasswordSalt]=@PasswordSalt, [PasswordFormat]=@PasswordFormat, [PwdLastSet]=GetDate() WHERE @UserName = [UserName]
	IF @@ROWCOUNT > 0
	BEGIN
		SELECT 1
	END
END

GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROCEDURE [dbo].[sp_GetPasswordWithFormat]
    @UserName varchar(60)
AS
BEGIN
    SELECT  [Password], PasswordFormat, PasswordSalt
    FROM    dbo.UserTable
    WHERE   @UserName = UserName
END
GO

CREATE PROCEDURE [dbo].[sp_UnLockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',LOCKED','')
			SET @status = REPLACE(@status, 'LOCKED,','')
			SET @status = REPLACE(@status, 'LOCKED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_LockUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('LOCKED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',LOCKED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('LOCKED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_DisableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) = 0
		BEGIN
			UPDATE UserTable SET AccountStatus = CONCAT(@status,',DISABLED') WHERE UserName=@UserName
		END
	END
	ELSE
	BEGIN
		UPDATE UserTable SET AccountStatus = 'DISABLED' WHERE UserName=@UserName
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) > 0 AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_EnableUser]
	@UserName varchar(60)
AS
BEGIN
	DECLARE @status varchar (200)

	SELECT @status = AccountStatus FROM UserTable WHERE UserName=@UserName
	IF (LEN(@status) > 0)
	BEGIN
		IF charindex('DISABLED', @status) > 0
		BEGIN
			SET @status = REPLACE(@status, ',DISABLED','')
			SET @status = REPLACE(@status, 'DISABLED,','')
			SET @status = REPLACE(@status, 'DISABLED','')
			UPDATE UserTable SET AccountStatus = @status WHERE UserName=@UserName
		END
	END

	SELECT UserName FROM UserTable WHERE charindex('DISABLED', AccountStatus) = 0 AND UserName=@UserName
END
GO

CREATE LOGIN SecureAuthSQLUser WITH PASSWORD = 'Password';
CREATE USER SecureAuthSQLUser FOR LOGIN SecureAuthSQLUser;
CREATE ROLE db_serviceaccount
EXEC sp_addrolemember 'db_serviceaccount', 'SecureAuthSQLUser'
GRANT EXECUTE ON [UserStore].dbo.sp_LockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UnlockUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_CreateUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUser TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_UpdateUserProfileJson TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.ObjectTable TO db_serviceaccount
GRANT EXECUTE ON TYPE::[UserStore].dbo.JsonTable TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_GetPasswordWithFormat TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_ResetPassword TO db_serviceaccount
GRANT EXECUTE ON [UserStore].dbo.sp_AddUserToGroup TO db_serviceaccount

In this section:

SQL user data store tables and stored procedures configuration

Prerequisites

Note

DELETE User

SQL Table Samples

User Table

CREATE TABLE

CREATE TABLE (JSON)

Object Table Type

CREATE TABLE Type

CREATE TABLE Type (JSON)

Fingerprint Table

CREATE TABLE for Browser / Device Fingerprinting

CREATE TABLE (JSON) for Browser / Device Fingerprinting

Push Notification Table

CREATE TABLE for Push Notification

CREATE TABLE (JSON) for Push Notification

Access History Table

CREATE TABLE for User Access History

CREATE TABLE (JSON) for User Access History

OATH Token Table

CREATE TABLE for User OATH Token

CREATE TABLE (JSON) for User OATH Token

Indexes

CREATE Indexes

CREATE Indexes (JSON)

Stored procedure samples - Membership

Get User

CREATE PROCEDURE Get UserName

Reset Password

CREATE PROCEDURE Reset Password

Get Password with Format

CREATE PROCEDURE Get Password with Format

Create User

CREATE PROCEDURE Create User

Update Group List

CREATE PROCEDURE Update Group List

Stored procedure samples - Profile

Get User Profile

CREATE PROCEDURE Get User Profile

CREATE PROCEDURE Get User Profile (JSON)

Update User Profile

CREATE PROCEDURE Update User Profile

CREATE PROCEDURE Update User Profile (JSON)

Lock User

CREATE PROCEDURE Lock User Stored Procedure

Unlock User

CREATE PROCEDURE Unlock User Stored Procedure

Disable User

CREATE PROCEDURE Disable User Stored Procedure

Enable User

CREATE PROCEDURE Enable User Stored Procedure

Roles and Permissions

Note

CREATE Role and User

Create Database scripts

Create Database

CREATE Database script

Create Database (JSON)

CREATE Database (JSON) script

Create Database scripts – Amazon RDS for SQL Server in AWS

Create Database - Amazon RDS for SQL

CREATE Database script

Create Database (JSON) - Amazon RDS for SQL

CREATE Database (JSON) script

Search results