USE [Elyse_DB]
GO
/****** Object:  Table [user_restr].[user_role_link]    Script Date: Sat 05-09-2026 7:01:54 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [user_restr].[user_role_link](
	[role_name] [nvarchar](50) 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_user_role_link] PRIMARY KEY CLUSTERED 
(
	[role_name] 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].[user_role_link] ADD  CONSTRAINT [DF_user_role_link_created]  DEFAULT (sysdatetime()) FOR [created]
GO
ALTER TABLE [user_restr].[user_role_link] ADD  CONSTRAINT [DF_user_role_link_granted_by]  DEFAULT (original_login()) FOR [granted_by]
GO
ALTER TABLE [user_restr].[user_role_link]  WITH CHECK ADD  CONSTRAINT [FK_role_has_user_sid] FOREIGN KEY([sid_id])
REFERENCES [user_restr].[sid_list] ([sid_id])
GO
ALTER TABLE [user_restr].[user_role_link] CHECK CONSTRAINT [FK_role_has_user_sid]
GO
ALTER TABLE [user_restr].[user_role_link]  WITH CHECK ADD  CONSTRAINT [FK_user_has_role] FOREIGN KEY([role_name])
REFERENCES [user_restr].[role_list] ([role_name])
GO
ALTER TABLE [user_restr].[user_role_link] CHECK CONSTRAINT [FK_user_has_role]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table links a user sid with a defined role.  It is used by stored procedures to check that a connected user has been granted permission to the given application role.  For a function to be executed, the application being used by the user must know the password to set the given application role which gives access to the schema which owns the stored procedure, and the user must also be listed in the database against the corresponding role in the role_list table.  

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