Membership Schema

Project: SqlServer

Layout: Membership

 

Click on a table to jump to the field-level descriptions for the table.

 

Audit

AuditId

bigint NOT NULL AUTOINCREMENT

AuditTableId

int NOT NULL

TableRecordPrimaryKeyID

nvarchar( 128 ) NOT NULL

ActivityType

char( 1 ) NOT NULL

ChangeDescription

xml NOT NULL

AuditDate

datetime DEFO getdate()

AuditUserNameHash

binary( 20 )

Indexes

PK_Membership_Audit primary key

ON AuditId

IX_Audit_Created_LastMod

ON AuditTableId, TableRecordPrimaryKeyID, AuditId

Foreign Keys

FK_Membership_Audit_AuditTable

( AuditTableId ) ref AuditTable (AuditTableId)

 

AuditTable

AuditTableId

int NOT NULL AUTOINCREMENT

TableSchema

nvarchar( 128 ) NOT NULL

TableName

nvarchar( 128 ) NOT NULL

Indexes

PK_Enterprise_AuditTable primary key

ON AuditTableId

 

CaseProductEnvironmentGroup

CaseProductEnvironmentGroupId

int NOT NULL AUTOINCREMENT

CaseProductEnvironmentId

int NOT NULL

GroupId

int NOT NULL

Indexes

PK_CaseGroup primary key

ON CaseProductEnvironmentGroupId

IXU_CaseProductEnvironemtGroup unique

ON CaseProductEnvironmentId, GroupId

Foreign Keys

FK_CaseGroup_CaseProductEnvironment

( CaseProductEnvironmentId ) ref CaseProductEnvironment (CaseProductEnvironmentId)

FK_CaseGroup_Group

( GroupId ) ref Group (GroupId)

 

CaseProductEnvironmentGroupPrivileges

GroupId

int NOT NULL

CaseProductEnvironmentId

int NOT NULL

PrivilegeId

int NOT NULL

Indexes

PK_CaseGroupPrivileges primary key

ON GroupId, CaseProductEnvironmentId, PrivilegeId

idx_CaseGroupPrivileges

ON CaseProductEnvironmentId

Foreign Keys

FK_CaseGroupPrivileges_CaseProductEnvironment

( CaseProductEnvironmentId ) ref CaseProductEnvironment (CaseProductEnvironmentId)

FK_Memebership_CaseGroupPrivilege_Group

( GroupId ) ref Group (GroupId)

FK_Memebership_CaseGroupPrivilege_PrivilegeId

( PrivilegeId ) ref Privilege (PrivilegeId)

 

EntityReportMap

EntityReportMapId

int NOT NULL AUTOINCREMENT

EntityName

nvarchar( 50 ) NOT NULL

EntityPrimaryKeyId

nvarchar( 50 ) NOT NULL

EntityHmac

nvarchar( 50 ) NOT NULL

Indexes

PK_EntityReportMap primary key

ON EntityReportMapId

 

Group

GroupId

int NOT NULL AUTOINCREMENT

ManagingClientId

int NOT NULL

Name

nvarchar( 50 ) NOT NULL

Description

nvarchar( 256 )

IsActive

bit NOT NULL

IsHost

bit NOT NULL

Indexes

PK_Group primary key

ON GroupId

IXU_Group_Name_ManagingClientId unique

ON Name, ManagingClientId

Foreign Keys

FK_Group_ManagingClient

( ManagingClientId ) ref ManagingClient (ManagingClientId)

 

GroupUser

GroupUserId

int NOT NULL AUTOINCREMENT

GroupId

int NOT NULL

UserId

uniqueidentifier NOT NULL

Indexes

PK_GroupUser primary key

ON GroupUserId

IXU_GroupUser_GroupId_UserId unique

ON GroupId, UserId

idx_GroupUser

ON UserId

Foreign Keys

FK_GroupUser_Group

( GroupId ) ref Group (GroupId)

FK_GroupUser_User

( UserId ) ref Users (UserId)

 

LoginTrustedIp

TrustedIpId

int NOT NULL AUTOINCREMENT

UserId

uniqueidentifier NOT NULL

Ip

nvarchar( 39 ) NOT NULL

Indexes

PK_Membership_LoginTrustedIp primary key

ON TrustedIpId

IX_LoginTrustedIp_LoginId

ON UserId

Foreign Keys

FK_Membership_LoginTrustedIp_Users

( UserId ) ref Users (UserId)

 

Privilege

PrivilegeId

int NOT NULL

PrivilegeName

nvarchar( 50 ) NOT NULL

Indexes

PK_Membership_Privilege primary key

ON PrivilegeId

 

Template

TemplateId

int NOT NULL AUTOINCREMENT

TemplateName

nvarchar( 50 ) NOT NULL

ManagingClientId

int NOT NULL

Indexes

PK_Membership_Template primary key

ON TemplateId

Foreign Keys

FK_Membership_Template_ManagingClient

( ManagingClientId ) ref ManagingClient (ManagingClientId)

 

TemplatePrivileges

TemplatePrivilegeId

int NOT NULL AUTOINCREMENT

TemplateId

int NOT NULL

PrivilegeId

int NOT NULL

Indexes

PK_Membership_TemplatePrivileges primary key

ON TemplatePrivilegeId

UK_Membership_TemplatePrivilege unique

ON TemplateId, PrivilegeId

Foreign Keys

FK_Membership_TemplatePrivileges_Privilege

( PrivilegeId ) ref Privilege (PrivilegeId)

FK_Membership_TemplatePrivileges_Template

( TemplateId ) ref Template (TemplateId)

 

UserType

UserTypeId

int NOT NULL AUTOINCREMENT

Name

nvarchar( 256 ) NOT NULL

Notes

nvarchar(max)

Indexes

PK_Membership_UserType primary key

ON UserTypeId

 

Users

UserId

uniqueidentifier NOT NULL DEFO newid()

CompanyId

int

FirstName

nvarchar( 50 )

LastName

nvarchar( 50 )

Phone

nvarchar( 50 )

Notes

nvarchar(max)

Username

nvarchar( 256 ) NOT NULL

IsPredefined

bit NOT NULL DEFO 0

IsLockedOut

bit NOT NULL

PasswordHash

nvarchar( 128 )

PasswordSalt

nvarchar( 128 )

LastLockOutDate

datetime

FailedPasswordAttemptCount

int NOT NULL

ForceNewPassword

bit NOT NULL DEFO 0

LastLoginDate

datetime

LastActivityDate

datetime

LastPasswordChangeDate

datetime

IsActiveDirectory

bit NOT NULL

UserTypeId

int

BillingRate

decimal( 18, 2 )

ManagingClientId

int

UsernameHash

binary( 20 )

IsActive

bit NOT NULL DEFO 0

Indexes

PK_Membership_Users primary key

ON UserId

IXU_Membership_Users_UniqueUserName unique

ON Username

IX_Users_UsernameHashToUserName

ON UsernameHash, Username

Foreign Keys

FK_User_UserType

( UserTypeId ) ref UserType (UserTypeId)

 

View vAuditCreatedByModifiedBy

 

View vUser

 

View vUserPassword

________________

Enterprise 2017.4.0