USE [Elyse_DB]
GO
/****** Object:  StoredProcedure [authorising].[usp_DEL_sid]    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: 02-10-2023
-- Description:	Initial creation
-- Revokes a uses from the system by replacing the sid with a unique but invalid value.  
-- The user can be restored by copying the sid back from sid_restore. 
-- Input is the SID ID.
-- Output is a status message and a transaction status.  
/*
COPYRIGHT NOTICE
This database schema and stored procedures are protected by copyright.
Copyright.  Silkwood Software Pty. Ltd. 2023
*/
-- =============================================
CREATE PROCEDURE [authorising].[usp_DEL_sid] 

     @sidrecordid bigint               = NULL,    -- SID ID record number (not the SID)  
	 @notes nvarchar(1000)             = NULL,  -- Optional
	 @app_reference nvarchar(1000)     = '',    -- Optional
	 @message nvarchar(1000)           = NULL OUTPUT,
	 @transaction_status nvarchar(50)  = NULL OUTPUT

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

  DECLARE 
        @tempmessage nvarchar(300)             = '',
	    @transactionmessage nvarchar(300)      = '',     -- Communicates what was the outcome of the transaction
	    @validationmessage nvarchar(200)       = '',     -- Communicates that the transaction failed due to data validation
		@nopermissionmessage nvarchar(200)     = '',     -- Communicates that the user does not have the required permission
		@recordnotexistsmessage nvarchar(200)  = '',     -- Communicates that a matching record does not exist
		@isauthorisermessage nvarchar(200)     = '',     -- Communicates that the user is an authoriser and cannot be deleted via this procedure.
		@username nvarchar(150)                = ORIGINAL_LOGIN(),     -- The username 
		@connectedusersid varbinary(100)       = SUSER_SID(ORIGINAL_LOGIN()),   -- The SID of the connected user
		@userauthentication_status nchar(10)   = 'Fail', -- The outcome of the authentication check of the user 
		@transactionmessage2 nvarchar(200)     = '',
		@transaction_ready nchar(10)           = 'Ready',
		@data_validation_status nchar(10)      = 'Pass';

  -- Parameters which have not been explicitly set might be output as null to calling functions
  SET @transaction_status = 'Transaction not attempted';  

  -- Connected user authentication
  -- Authenticate the connected user as an authoriser
  EXEC [internal].[usp_AUTHENTICATE_authoriser] 
		@user_authentication_result = @userauthentication_status OUTPUT;
  IF @userauthentication_status = 'Fail'
    BEGIN  -- The user does not have permission for this action
		SET @transaction_ready      = 'Fail';
	    EXEC internal.usp_SEL_message 
            @message_id   = 'NoPermission', 
			@message_text = @tempmessage OUTPUT;
	    IF (@tempmessage IS NOT NULL) 
  	       SET @nopermissionmessage = @username + '  ' + @tempmessage;
	    ELSE 
		   SET @nopermissionmessage = 'A database level message error occurred on NoPermission.';
	END


  IF @userauthentication_status = 'Pass' -- Don't do anything if the user is not authorised.
    BEGIN
	  -- Data validation
	   -- Check that the sid is valid
		 IF @sidrecordid = 0
		    SET @sidrecordid = NULL;
		 IF NOT EXISTS (SELECT sid_id 
				  	      FROM user_restr.sid_list 
					     WHERE sid_id = @sidrecordid) 
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'UserIdNotExist', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  				SET @recordnotexistsmessage = ISNULL(CONVERT(nvarchar, @sidrecordid), 'NULL') + '  ' + @tempmessage;
			 ELSE 
				  SET @recordnotexistsmessage = 'A database level message error occurred on UserIdNotExist';
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END

       -- End checking that the sid  is valid

	   -- Check if the sid is assigned a role as authoriser.  
       IF EXISTS (SELECT authoriser_sid_id
	                FROM user_restr.authorisers
				   WHERE authoriser_sid_id = @sidrecordid)
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'IsAuthoriser', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  				SET @isauthorisermessage = ISNULL(CONVERT(nvarchar, @sidrecordid), 'NULL') + '  ' + @tempmessage;
			 ELSE 
				  SET @isauthorisermessage = 'A database level message error occurred on IsAuthoriser';
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END

	-- Check if the user is already de-activated.
      IF @data_validation_status = 'Pass'
	    BEGIN
		 IF EXISTS (SELECT sid_id 
			 	      FROM user_restr.sid_list 
				     WHERE sid_id = @sidrecordid
					   AND sid = 0) 
		   BEGIN
			 EXEC internal.usp_SEL_message 
				  @message_id   = 'UserDeactivated', 
				  @message_text = @tempmessage OUTPUT;
			 IF (@tempmessage IS NOT NULL) 
  				SET @recordnotexistsmessage = ISNULL(CONVERT(nvarchar, @sidrecordid), 'NULL') + '  ' + @tempmessage;
			 ELSE 
				  SET @recordnotexistsmessage = 'A database level message error occurred on UserDeactivated';
			 SET @data_validation_status = 'Fail';
			 SET @transaction_ready      = 'Fail';
		   END
		END




	  -- Output the data validation status failed message
	  IF @data_validation_status = 'Fail'
		BEGIN
		  EXEC internal.usp_SEL_message 
			   @message_id   = 'FailedDataValidation', 
			   @message_text = @tempmessage OUTPUT;
		  IF (@tempmessage IS NOT NULL) 
			 SET @transactionmessage = @tempmessage;
		  ELSE 
			   SET @transactionmessage = 'A database level message error occurred on FailedDataValidation.';
		END
	  -- End data validation
  END -- End of IF authentication status = Pass.

-- Execute the insert query
  IF @transaction_ready = 'Ready'
	BEGIN
	  BEGIN TRY 

	   BEGIN TRANSACTION;

	   -- Write to the revoke log
	  INSERT INTO user_restr.user_privilege_revoke_log 
	              (privilege, privilege_name, sid_id, username, granted_on, granted_by, revoked_by)
	       VALUES ('SID',     'SID Revoked',       @sidrecordid, 
		          (SELECT username FROM user_restr.sid_list
				                  WHERE         sid_id 
										= @sidrecordid),
		          (SELECT created FROM user_restr.sid_list
				                  WHERE         sid_id 
										= @sidrecordid),
				  '',
				  @username)

         -- Write to the privilege log 

		 INSERT INTO user_restr.user_privilege_log
		             (privilege_type,                  privilege_name,           sid_id,      username, action_type, 
					 created_by_sid_id, valid_from, valid_until, notes, app_reference)
			  VALUES ('SID', 'Global',         @sidrecordid, 
			   (SELECT username
		          FROM user_restr.sid_list
				 WHERE sid_id = @sidrecordid), 
			   'Revoke',
			         (SELECT sid_id
		                FROM user_restr.sid_list
				   WHERE sid = @connectedusersid), 
					 NULL, NULL, @notes, @app_reference)


          -- Complete the revoke transaction
			UPDATE user_restr.sid_list
			SET sid =
				-- Invalid SID on purpose, but must be unique:
				-- Revision = 0x00  (invalid, must be 1)
				-- SubAuthorityCount = 0x01
				-- IdentifierAuthority = NULL (0x000000000000)
				-- SubAuthority[0] = sid_id (ensures uniqueness)
				0x00                                   -- Revision (invalid)
			  + 0x01                                   -- SubAuthority count
			  + 0x000000000000                         -- NULL identifier authority (invalid)
			  + CAST(sid_id AS BINARY(4))              -- Unique payload
			WHERE sid_id = @sidrecordid;




	    COMMIT TRANSACTION;

         EXEC internal.usp_SEL_message 
              @message_id   = 'Success', 
              @message_text = @tempmessage OUTPUT;
  	     IF (@tempmessage IS NOT NULL) 
	        SET @transactionmessage = @tempmessage;
	     ELSE 
		      SET @transactionmessage = 'A database level message error occurred on Success.';
		 SET @transaction_status = 'Good';

   	  END TRY
	  BEGIN CATCH

	     IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

	     SET @transaction_status = 'Bad';
         EXEC internal.usp_SEL_message 
              @message_id   = 'DeleteError', 
              @message_text = @tempmessage OUTPUT;
		 IF (@tempmessage IS NOT NULL) 
		    SET @transactionmessage = @tempmessage + ' | ' + 
		    CONVERT(nvarchar(10),ERROR_NUMBER()) + ' | ' + ERROR_MESSAGE();
		 ELSE 
		      SET @transactionmessage = 'A database level message error occurred on DeleteError.';
	  END CATCH
    END

-- Concatenate the messages
/*
NOTE: "The + (String Concatenation) operator behaves differently when it works with an empty, 
zero-length string than when it works with NULL, or unknown values. A zero-length 
character string can be specified as two single quotation marks without any characters 
inside the quotation marks. A zero-length binary string can be specified as 0x without 
any byte values specified in the hexadecimal constant. Concatenating a zero-length string 
always concatenates the two specified strings. When you work with strings with a null value, 
the result of the concatenation depends on the session settings. Just like arithmetic 
operations that are performed on null values, when a null value is added to a known 
value the result is typically an unknown value, a string concatenation operation that 
is performed with a null value should also produce a null result." 
*/
    IF (@message = '' OR @message IS NULL) SET @message = ' ';
    IF (@transactionmessage <> '')         SET @message = @transactionmessage;
    IF (@validationmessage <> '')          SET @message = @message + ' | ' + @validationmessage;
	IF (@recordnotexistsmessage <> '')     SET @message = @message + ' | ' + @recordnotexistsmessage;
    IF (@transactionmessage2 <> '')        SET @message = @message + ' | ' + @transactionmessage2;
	IF (@nopermissionmessage <> '')        SET @message = @message + ' | ' + @nopermissionmessage;
	IF (@isauthorisermessage <> '')        SET @message = @message + ' | ' + @isauthorisermessage;





END
GO