SQL user data store tables and stored procedures configuration (9.1 to 19.07.01-10)

Updated June 29, 2020

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

Note

If the JSON Data Format is selected for Fingerprints, Push Notification Tokens, OATH Tokens, 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 the following screenshot of Profile Fields with JSON Data Format selected...

60559406.png

SQL Table Samples

User Table

This single table contains the User ID, Password, and Profile Information.

CREATE TABLE [dbo].[UserTable](
        [UserName] [varchar](60) NOT NULL,
        [Password] [varchar](60) 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

Object Table Type

This script creates the Object Table Type, which is used for Fingerprint and PUSH Notification token information.

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

If Fingerprint and PUSH Notification token information is configured as JSON, then use this script to create the Object Table Type.

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

This table contains Browser / Device Fingerprinting information for each user.

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

PUSH Notification Table

This table contains PUSH Notification token information for each user.

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

OATH Token Table

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

Access History Table

This table contains Access History information for each user.

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

Stored Procedure Samples

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

Membership Stored Procedures

Get User

This stored procedure checks if the username exists, and returns that same username in the case that it does.

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

This stored procedure resets the password for the given user.

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

This stored procedure gets the password of the user for validation.

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

Create User

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

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

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]) VALUES (@UserName, @Password, @PasswordSalt, @PasswordFormat)
 
	IF @@ROWCOUNT > 0 
	SELECT @Status = 0
ELSE
	SELECT @Status = 1
END
GO

Update Group List

This stored procedure is utilized on the Create User page to update a user's GroupList column.

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

Profile Stored Procedures

The sample stored procedures below include all profile information and they must be modified to match the profile information that maps to the SQL profile provider in the Identity Platform Web Admin (Data tab).

For example, if only using SQL to store and retrieve Fingerprint information, then only the Fingerprint parameter needs to be sent by the Identity Platform.

Get User Profile

This stored procedure retrieves the profile of the given username.

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
    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

Get User Profile (JSON)

This stored procedure retrieves the profile of the given username if using JSON.

USE [UserStore]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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

Update User Profile

This stored procedure updates the user profile with the given profile information.

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

Update User Profile (JSON)

This stored procedure updates the user profile with the given profile information, if using JSON.

USE [UserStore]
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

Lock User

This stored procedure locks an end-user account.

CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS

BEGIN
  UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
  SELECT UserName FROM UserTable WHERE AccountStatus = 'LOCKED' AND UserName=@UserName
END
SET NOCOUNT ON;

Unlock User

This stored procedure unlocks an end-user account.

CREATE PROCEDURE [dbo].[sp_UnLockUser]
@UserName varchar(60)
AS

BEGIN
  UPDATE UserTable SET AccountStatus = " WHERE UserName=@UserName
  SELECT UserName FROM UserTable WHERE AccountStatus = " AND UserName=@UserName
END

Roles and Permissions

This script creates a service account user, service account role, and adds the user to the role.

Note

Change the username SecureAuthSQLUser and password Password to the actual values.

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

All-inclusive Create Database scripts

Note

If you have a display issue with not being able to view over 600 lines of code, you can download the sql scripts located in the following sections.

Create Database

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

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,
        [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].[UserFP] (
        [UserName] [varchar](60) NOT NULL
        ,[DigitalFP] [varbinary](max) NOT NULL
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

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

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

CREATE TABLE [dbo].[UserOT] (
        [UserName] [varchar](60) NOT NULL
        ,[OATHToken] [varbinary](max) NOT NULL
        ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
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
    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], [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
        UPDATE UserTable SET AccountStatus = '' WHERE UserName=@UserName
        SELECT UserName FROM UserTable WHERE AccountStatus = '' AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
        UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
        SELECT UserName FROM UserTable WHERE AccountStatus = 'LOCKED' 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 CreateDatabaseJson01.sql script creates a "UserStore" database and also creates all of the above tables, objects, and stored procedures within that database in JSON format.

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](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
    UPDATE UserTable SET AccountStatus = '' WHERE UserName=@UserName
    SELECT UserName FROM UserTable WHERE AccountStatus = '' AND UserName=@UserName
END
GO

CREATE PROCEDURE [dbo].[sp_LockUser]
@UserName varchar(60)
AS
BEGIN
    UPDATE UserTable SET AccountStatus = 'LOCKED' WHERE UserName=@UserName
    SELECT UserName FROM UserTable WHERE AccountStatus = 'LOCKED' 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