Summary Table
Categories |
Total Count |
PII |
0 |
URL |
0 |
DNS |
0 |
EKL |
0 |
IP |
0 |
PORT |
0 |
VsID |
0 |
CF |
0 |
AI |
0 |
VPD |
0 |
PL |
0 |
Other |
0 |
File Content
?CREATE TABLE seclyr.UserRoles
(
--fields
UserRoleID [int] IDENTITY(1,1) NOT NULL,
UserID [int] NOT NULL,
RoleID [tinyint] NOT NULL,
AccessLevelID [tinyint] NOT NULL,
ModuleID [tinyint] NOT NULL,
SensitivityLevelID [tinyint] NOT NULL,
RegionID [tinyint],
CCNRegionID tinyint,
VisnID [tinyint],
StationID [smallint],
CreatedByUserID [int],
UpdatedByUserID [int],
DateCreated [datetime2](7),
DateUpdated [datetime2](7),
AccessStatus [varchar] (30) NOT NULL,
PointOfContact [bit] NOT NULL,
CanEdit [bit] NOT NULL,
Comment [varchar](200),
--primary key
CONSTRAINT PK_UserRoles PRIMARY KEY CLUSTERED
(
UserRoleID ASC
),
----unique key
--CONSTRAINT UK_UserRoles UNIQUE NONCLUSTERED
--(
-- UserID ASC,
-- RoleID ASC,
-- AccessLevelID ASC,
-- ModuleID ASC,
-- RegionID ASC,
-- VisnID ASC,
-- StationID ASC
--),
)
ON DefinitionData
--when using clustered columnstore index, can't set data compression to PAGE
WITH (DATA_COMPRESSION = PAGE)
GO
--foreign keys
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT [FK_UserRoles_CreatedByUserID]
FOREIGN KEY (CreatedByUserID)
REFERENCES seclyr.Users (UserID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT [FK_UserRoles_UpdatedByUserID]
FOREIGN KEY (UpdatedByUserID)
REFERENCES seclyr.Users (UserID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_Roles
FOREIGN KEY (RoleID)
REFERENCES seclyr.Roles (RoleID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_Users
FOREIGN KEY (UserID)
REFERENCES seclyr.Users (UserID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_AccessLevels
FOREIGN KEY (AccessLevelID)
REFERENCES seclyr.AccessLevels (AccessLevelID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_Modules
FOREIGN KEY (ModuleID)
REFERENCES seclyr.Modules (ModuleID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_SensitivityLevels
FOREIGN KEY (SensitivityLevelID)
REFERENCES seclyr.SensitivityLevels (SensitivityLevelID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_RegionID
FOREIGN KEY (RegionID)
REFERENCES dbo.Regions (RegionID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_CCNRegionID
FOREIGN KEY (CCNRegionID)
REFERENCES dbo.Regions (RegionID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_Visns
FOREIGN KEY (VisnID)
REFERENCES dbo.Visns (VisnID)
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT FK_UserRoles_Stations
FOREIGN KEY (StationID)
REFERENCES dbo.Stations (StationID)
GO
--indexes
CREATE NONCLUSTERED INDEX IX_UserRoles_RoleID ON seclyr.UserRoles
(
RoleID ASC
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_UserRoles_UserID ON seclyr.UserRoles
(
UserID ASC
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_UserRoles_AccessLevelID ON seclyr.UserRoles
(
AccessLevelID ASC
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON DefinitionIndex
GO
CREATE NONCLUSTERED INDEX IX_UserRoles_AccessStatus ON seclyr.UserRoles
(
AccessStatus ASC
) WITH (
PAD_INDEX = ON, --If want to use a Fill Factor, then PAD_INDEX must = ON
FILLFACTOR = 90, --100 = max fill; set to 90 if going to insert new values incrementally; 100 if doing bulk load
SORT_IN_TEMPDB = ON, -- sorts the index in the TempDB; default = OFF
IGNORE_DUP_KEY = OFF,
STATISTICS_NORECOMPUTE = OFF,
STATISTICS_INCREMENTAL = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 0, --degrees of parallelism, controls how many CPUs to use; 0 = default (all available), max = 64
DATA_COMPRESSION = PAGE --use page compression; default = NONE
)
ON DefinitionIndex
GO
--constraints
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT DF_UserRoles_PointOfContact
DEFAULT 0
FOR PointOfContact
GO
ALTER TABLE seclyr.UserRoles
ADD CONSTRAINT DF_UserRoles_CanEdit
DEFAULT 0
FOR CanEdit
GO
--Metadata descriptions for each field
EXECUTE sp_addextendedproperty
N'MS_Description', N'Primary Key, unique to EPRS',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'UserRoleID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Users table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'UserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Roles table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'RoleID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to AccessLevels table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'AccessLevelID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Modules table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'ModuleID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Sensitivity Levels table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'SensitivityLevelID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table is associated with VHA Regions',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'RegionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Regions table is associated with CCN Regions',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'CCNRegionID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Visns table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'VisnID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Stations table',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'StationID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Users table, User id who created the record',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'CreatedByUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Foreign Key, unique to EPRS, relates to Users table, User id who last updated the record',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'UpdatedByUserID'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was created',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'DateCreated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Date record was last updated',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'DateUpdated'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Status of the user role',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'AccessStatus'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/false is the user a point of contact in this role',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'PointOfContact'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'True/false does the user have permission to edit records',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'CanEdit'
GO
EXECUTE sp_addextendedproperty
N'MS_Description', N'Comments about the user role',
N'SCHEMA', N'seclyr',
N'TABLE', N'UserRoles',
N'COLUMN', N'Comment'
GO