USE [Elyse_DB]
GO
/****** Object:  Table [user_restr].[doc_group_view_permissions]    Script Date: Sat 05-09-2026 7:01:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [user_restr].[doc_group_view_permissions](
	[doc_group_id] [bigint] NOT NULL,
	[sid_id] [bigint] NOT NULL,
	[created] [datetime2](7) NOT NULL,
	[granted_by] [nvarchar](128) NOT NULL,
	[valid_from] [datetime2](7) NULL,
	[valid_until] [datetime2](7) NULL,
	[notes] [nvarchar](1000) NULL,
	[app_reference] [nvarchar](1000) NULL,
 CONSTRAINT [PK_doc_group_view_permission] PRIMARY KEY CLUSTERED 
(
	[doc_group_id] ASC,
	[sid_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [user_restr].[doc_group_view_permissions] ADD  CONSTRAINT [DF_doc_group_view_permissions_created]  DEFAULT (sysdatetime()) FOR [created]
GO
ALTER TABLE [user_restr].[doc_group_view_permissions] ADD  CONSTRAINT [DF_doc_group_view_permissions_granted_by]  DEFAULT (original_login()) FOR [granted_by]
GO
ALTER TABLE [user_restr].[doc_group_view_permissions]  WITH CHECK ADD  CONSTRAINT [FK_doc_group_has_view_restriction] FOREIGN KEY([doc_group_id])
REFERENCES [xref].[doc_group_names] ([doc_group_id])
GO
ALTER TABLE [user_restr].[doc_group_view_permissions] CHECK CONSTRAINT [FK_doc_group_has_view_restriction]
GO
ALTER TABLE [user_restr].[doc_group_view_permissions]  WITH CHECK ADD  CONSTRAINT [FK_doc_group_view_permissions_has_sid] FOREIGN KEY([sid_id])
REFERENCES [user_restr].[sid_list] ([sid_id])
GO
ALTER TABLE [user_restr].[doc_group_view_permissions] CHECK CONSTRAINT [FK_doc_group_view_permissions_has_sid]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Example: A note regarding why this group of documents was linked to a particular SID.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'doc_group_view_permissions', @level2type=N'COLUMN',@level2name=N'notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table links a document group with security identifiers for the purpose of restricting view access to documents within a group to specific users.
The intent of a user table of user restrictions rather than using the MSQLS security architecture in this particular instance is so that the application layer will not need rights to change database permissions on the fly, or alternatively require developer input.  The intent is that the roles and permissions within the database are fixed and the allocation of individual users is implemented at the Active Directory level. 

COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'doc_group_view_permissions'
GO