USE [Elyse_DB]
GO
/****** Object:  Table [user_restr].[authoriser_privilege_requests]    Script Date: Sat 05-09-2026 7:01:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [user_restr].[authoriser_privilege_requests](
	[request_id] [bigint] IDENTITY(1,1) NOT NULL,
	[sid_id] [bigint] NOT NULL,
	[username] [nvarchar](128) NULL,
	[type] [nvarchar](20) NOT NULL,
	[approved_by_sid_id_1] [bigint] NULL,
	[approved_by_username_1] [nvarchar](128) NULL,
	[approved_on_1] [datetime2](7) NULL,
	[approved_by_sid_id_2] [bigint] NULL,
	[approved_by_username_2] [nvarchar](128) NULL,
	[approved_on_2] [datetime2](7) NULL,
	[status] [nvarchar](20) NOT NULL,
	[valid_from] [datetime2](7) NULL,
	[valid_until] [nchar](10) NULL,
	[notes] [nvarchar](1000) NULL,
	[app_reference] [nvarchar](1000) NULL,
 CONSTRAINT [PK_authoriser_privilege_requests] PRIMARY KEY CLUSTERED 
(
	[request_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].[authoriser_privilege_requests] ADD  CONSTRAINT [DF_authoriser_privilege_requests_status]  DEFAULT (N'Pending') FOR [status]
GO
ALTER TABLE [user_restr].[authoriser_privilege_requests]  WITH CHECK ADD  CONSTRAINT [FK_authoriser_privilege_requests_has_sid_id] FOREIGN KEY([sid_id])
REFERENCES [user_restr].[sid_list] ([sid_id])
GO
ALTER TABLE [user_restr].[authoriser_privilege_requests] CHECK CONSTRAINT [FK_authoriser_privilege_requests_has_sid_id]
GO
ALTER TABLE [user_restr].[authoriser_privilege_requests]  WITH CHECK ADD  CONSTRAINT [CK_authoriser_privilege_requests_status_check] CHECK  (([status]='Pending' OR [status]='Approved' OR [status]='Cancelled'))
GO
ALTER TABLE [user_restr].[authoriser_privilege_requests] CHECK CONSTRAINT [CK_authoriser_privilege_requests_status_check]
GO
ALTER TABLE [user_restr].[authoriser_privilege_requests]  WITH CHECK ADD  CONSTRAINT [CK_authoriser_privilege_requests_type_check] CHECK  (([type]='Grant' OR [type]='Revoke'))
GO
ALTER TABLE [user_restr].[authoriser_privilege_requests] CHECK CONSTRAINT [CK_authoriser_privilege_requests_type_check]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'See check constraints.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'authoriser_privilege_requests', @level2type=N'COLUMN',@level2name=N'type'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Note that there is intentionally no reference key back to the SID list.  ' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'authoriser_privilege_requests', @level2type=N'COLUMN',@level2name=N'approved_by_sid_id_1'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Note that there is intentionally no reference key back to the SID list.  ' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'authoriser_privilege_requests', @level2type=N'COLUMN',@level2name=N'approved_by_sid_id_2'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'See check constraints.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'authoriser_privilege_requests', @level2type=N'COLUMN',@level2name=N'status'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This field allows for a reference link to be created between an external application and the Elyse database.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'authoriser_privilege_requests', @level2type=N'COLUMN',@level2name=N'notes'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table handles pending requests for authoriser privilege granting or revoking.  See authorising.usp_INS_grant_authoriser, authorising.usp_INS_revoke_authoriser and authorising.usp_UPD_cancel_authoriser_requ.

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