Tables [dbo].[UserAccess]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)52
Created5:32:30 PM Wednesday, January 2, 2008
Last Modified7:33:52 PM Thursday, August 29, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Cluster Primary Key PK_UserAccess: UserAccessPriKeyUserAccessPriKeyint4
No
1 - 1
Check Constraints CK_UserAccess_ReportingUserAccess : ([dbo].[UserIdInReportingUserAccess]([UserID])=(0))Indexes IX_UserAccess_UserID: UserIDUserIDvarchar(32)32
No
Passwordvarbinary(max)max
No
Foreign Keys FK_UserAccess_SecurityProfile: [dbo].[SecurityProfile].SecProfilePriKeySecProfilePriKeyint4
No
Foreign Keys FK_UserAccess_ReportProfile: [dbo].[ReportProfile].RepProfilePriKeyRepProfilePriKeyint4
No
Foreign Keys FK_UserAccess_Sysuser: [dbo].[Sysuser].SysUserIDIndexes IX_UserAccess_SysUserID: SysUserIDSysUserIDint4
No
Foreign Keys FK_UserAccess_AdminProfile: [dbo].[AdminProfile].AdminProfileFIDAdminProfileFIDint4
Yes
ChangePasswordFlagbit1
No
((0))
Indexes IX_UserAccess_DomainUserName: DomainUserNameDomainUserNamevarchar(256)256
Yes
DomainUserNameSvarchar(12)12
Yes
DomainUserNameHvarchar(44)44
Yes
LastLoggedInMssdatetime8
Yes
Indexes IX_UserAccess_AadUserPrincipalName: AadUserPrincipalNameAadUserPrincipalNamenvarchar(128)256
Yes
Indexes Indexes
NameColumnsUniqueFilteredFill Factor
Cluster Primary Key PK_UserAccess: UserAccessPriKeyPK_UserAccessUserAccessPriKey
Yes
80
IX_UserAccess_AadUserPrincipalNameAadUserPrincipalName
Yes
Yes
IX_UserAccess_SysUserIDSysUserID
Yes
80
IX_UserAccess_UserIDUserID
Yes
IX_UserAccess_DomainUserNameDomainUserName
Check Constraints Check Constraints
NameOn ColumnConstraint
CK_UserAccess_ReportingUserAccessUserID([dbo].[UserIdInReportingUserAccess]([UserID])=(0))
Foreign Keys Foreign Keys
NameColumns
FK_UserAccess_AdminProfileAdminProfileFID->[dbo].[AdminProfile].[AdminProfileID]
FK_UserAccess_ReportProfileRepProfilePriKey->[dbo].[ReportProfile].[ReportProfilePriKey]
FK_UserAccess_SecurityProfileSecProfilePriKey->[dbo].[SecurityProfile].[SecProfilePriKey]
FK_UserAccess_SysuserSysUserID->[dbo].[Sysuser].[SysUserID]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
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
Uses
Used By