USE [Elyse_DB]
GO
/****** Object:  Table [user_restr].[form_group_owners]    Script Date: Sat 05-09-2026 7:01:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [user_restr].[form_group_owners](
	[form_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_form_owners] PRIMARY KEY CLUSTERED 
(
	[form_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].[form_group_owners] ADD  CONSTRAINT [DF_form_group_owners_created]  DEFAULT (sysdatetime()) FOR [created]
GO
ALTER TABLE [user_restr].[form_group_owners] ADD  CONSTRAINT [DF_form_group_owners_granted_by]  DEFAULT (original_login()) FOR [granted_by]
GO
ALTER TABLE [user_restr].[form_group_owners]  WITH CHECK ADD  CONSTRAINT [FK_form_owners_has_sid_id] FOREIGN KEY([sid_id])
REFERENCES [user_restr].[sid_list] ([sid_id])
GO
ALTER TABLE [user_restr].[form_group_owners] CHECK CONSTRAINT [FK_form_owners_has_sid_id]
GO
ALTER TABLE [user_restr].[form_group_owners]  WITH CHECK ADD  CONSTRAINT [FK_user_owns_form_group] FOREIGN KEY([form_group_id])
REFERENCES [forms].[form_group_names] ([form_group_id])
GO
ALTER TABLE [user_restr].[form_group_owners] CHECK CONSTRAINT [FK_user_owns_form_group]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'For example: A note regarding why this user was added to this form owners group.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'form_group_owners', @level2type=N'COLUMN',@level2name=N'notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a list of users linked to forms which they own and can edit.

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'form_group_owners'
GO