CREATE TABLE [dbo].[UserAccess]
(
[UserAccessPriKey] [int] NOT NULL IDENTITY(1, 1),
[UserID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Password] [varbinary] (max) NOT NULL,
[SecProfilePriKey] [int] NOT NULL,
[RepProfilePriKey] [int] NOT NULL,
[SysUserID] [int] NOT NULL,
[AdminProfileFID] [int] NULL,
[ChangePasswordFlag] [bit] NOT NULL CONSTRAINT [DF_UserAccess_ChangePasswordFlag] DEFAULT ((0)),
[DomainUserName] [varchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DomainUserNameS] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DomainUserNameH] [varchar] (44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastLoggedInMss] [datetime] NULL,
[AadUserPrincipalName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [CK_UserAccess_ReportingUserAccess] CHECK (([dbo].[UserIdInReportingUserAccess]([UserID])=(0)))
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [PK_UserAccess] PRIMARY KEY CLUSTERED  ([UserAccessPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [IX_UserAccess_SysUserID] UNIQUE NONCLUSTERED  ([SysUserID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [IX_UserAccess_UserID] UNIQUE NONCLUSTERED  ([UserID]) ON [PRIMARY]
GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_UserAccess_AadUserPrincipalName] ON [dbo].[UserAccess] ([AadUserPrincipalName]) WHERE ([AadUserPrincipalName] IS NOT NULL) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_UserAccess_DomainUserName] ON [dbo].[UserAccess] ([DomainUserName]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [FK_UserAccess_AdminProfile] FOREIGN KEY ([AdminProfileFID]) REFERENCES [dbo].[AdminProfile] ([AdminProfileID])
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [FK_UserAccess_ReportProfile] FOREIGN KEY ([RepProfilePriKey]) REFERENCES [dbo].[ReportProfile] ([ReportProfilePriKey])
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [FK_UserAccess_SecurityProfile] FOREIGN KEY ([SecProfilePriKey]) REFERENCES [dbo].[SecurityProfile] ([SecProfilePriKey])
GO
ALTER TABLE [dbo].[UserAccess] ADD CONSTRAINT [FK_UserAccess_Sysuser] FOREIGN KEY ([SysUserID]) REFERENCES [dbo].[Sysuser] ([SysUserID])
GO
GRANT SELECT ON  [dbo].[UserAccess] TO [MssExec]
GRANT INSERT ON  [dbo].[UserAccess] TO [MssExec]
GRANT DELETE ON  [dbo].[UserAccess] TO [MssExec]
GRANT UPDATE ON  [dbo].[UserAccess] TO [MssExec]
GO