USE [Elyse_DB]
GO
/****** Object:  Table [user_restr].[sid_list]    Script Date: Sat 05-09-2026 7:01:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [user_restr].[sid_list](
	[sid_id] [bigint] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](200) NULL,
	[descr] [nvarchar](max) NULL,
	[sid] [varbinary](100) NOT NULL,
	[created] [datetime2](7) NULL,
	[email_address] [nvarchar](255) NULL,
	[username] [nvarchar](255) NULL,
	[restore_sid] [varbinary](100) NOT NULL,
 CONSTRAINT [PK_sid_list] PRIMARY KEY CLUSTERED 
(
	[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] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [UN_restore_sid_must_be_unique]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [UN_restore_sid_must_be_unique] ON [user_restr].[sid_list]
(
	[restore_sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object:  Index [UN_sid_must_be_unique]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [UN_sid_must_be_unique] ON [user_restr].[sid_list]
(
	[sid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO
ALTER TABLE [user_restr].[sid_list] ADD  CONSTRAINT [DF_sid_list_created]  DEFAULT (sysdatetime()) FOR [created]
GO
ALTER TABLE [user_restr].[sid_list] ADD  CONSTRAINT [DF_sid_list_username]  DEFAULT (original_login()) FOR [username]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The primary key is a big int rather than using the SID to make it easier to pass the table PK back and forth to the application layer, avoiding the need to pass binary values.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'sid_list', @level2type=N'COLUMN',@level2name=N'sid_id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'An editable username field.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'sid_list', @level2type=N'COLUMN',@level2name=N'name'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Security identifier.
The SID is stored in binary because SUSER_SID() returns a binary data type.
' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'sid_list', @level2type=N'COLUMN',@level2name=N'sid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The is the system username.  The purpose of storing the username is so that it can be established after the user''s account has been deleted, when SUSER_SNAME will not resolve.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'sid_list', @level2type=N'COLUMN',@level2name=N'username'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This field is used for storing the SID so that it can be restored again after being revoked.  A user is ''deleted'' by replacing the sid with 0.  The use can be restored by copying the sid back from restore_sid to the sid field.' , @level0type=N'SCHEMA',@level0name=N'user_restr', @level1type=N'TABLE',@level1name=N'sid_list', @level2type=N'COLUMN',@level2name=N'restore_sid'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This is a list of security identifiers for objects with restricted access.  

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. 

The sid list internal to the database ensures that historical details of people linked to objects are retained in the database and are not lost when parallel lists elsewhere are updated.

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