USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [internal].[usp_AUTHENTICATE_user_role]    Script Date: Sat 05-09-2026 7:01:55 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Silkwood Software
-- Create date: 03-08-2023
-- Description:	Checks whether the connected user has permission
-- for a given role. 
-- Input is the role to be checked.  
-- Output is a status string of 'Pass' or 'Fail'.
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/

-- =============================================
CREATE PROCEDURE [internal].[usp_AUTHENTICATE_user_role] 

	@role_to_check nvarchar(50) = '',
	@user_authentication_result nvarchar(10) OUTPUT


AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

  DECLARE 
  		@usersid varbinary(100)            = NULL, -- The SID of the connected user
		@sid_id bigint                     = NULL,
		@failure_type nvarchar(1000)       = NULL,
		@now datetime2(7)                  = SYSDATETIME(),
		@privilege_name nvarchar(50)       = '';
 
   SET @usersid = SUSER_SID(ORIGINAL_LOGIN()); -- Read the SID of the connected user

  		SELECT @sid_id = sid_id
          FROM user_restr.sid_list
         WHERE sid = @usersid;


 -- Check if the user has permission
  IF EXISTS (SELECT sl.sid_id AS si  
               FROM user_restr.sid_list AS sl
	     INNER JOIN user_restr.user_role_link AS urlk
		         ON urlk.sid_id
			        = sl.sid_id
		 INNER JOIN user_restr.role_list AS rl
		         ON urlk.role_name
			        = rl.role_name
			  WHERE rl.role_name  = @role_to_check
			   AND  sl.sid = @usersid  
			   AND (urlk.valid_from IS NULL OR urlk.valid_from <= @now)
			   AND (urlk.valid_until IS NULL OR urlk.valid_until >= @now)
			   )  
		         
      -- The user has permission for this action
      SET @user_authentication_result = 'Pass';
  ELSE
    SET @user_authentication_result = 'Fail'; 
--  ============================================================================================
    --  Role checking does not write to the failure log because every stored procedure that checks
	--  multiple roles will fail for every role check which is not the role for which the application
	--  is set.  Role authorisation failure is therefore routine rather than an exception.  
	--  See [internal].[usp_SEL_message].
--  ============================================================================================

END
GO