USE [Elyse_DB] GO /****** Object: StoredProcedure [authorising].[usp_DEL_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: Initial creation -- Deletes a record from user_restr.user_role_link -- Input is the sid_id of the user from the sid_list table, -- and the role. -- 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_user_role] @role_to_delete nvarchar(50) = '', -- Role to revoke @user_sid_id_to_delete bigint = NULL, -- User sid_id from the sid_list table @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 require permission @recordnotexistmessage nvarchar(200) = '', -- Communicates that a the record does not exist @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 @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 if a matching record does not exist IF NOT EXISTS (SELECT role_name, sid_id FROM user_restr.user_role_link WHERE role_name = @role_to_delete AND sid_id = @user_sid_id_to_delete) BEGIN EXEC internal.usp_SEL_message @message_id = 'NotExist', @message_text = @tempmessage OUTPUT; IF (@tempmessage IS NOT NULL) SET @recordnotexistmessage = @tempmessage; ELSE SET @recordnotexistmessage = 'A database level message error occurred on NotExist'; SET @data_validation_status = 'Fail'; SET @transaction_ready = 'Fail'; 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 delete query IF @transaction_ready = 'Ready' BEGIN BEGIN TRY BEGIN TRANSACTION; INSERT INTO user_restr.user_privilege_revoke_log -- Create an audit log entry (privilege, privilege_name, sid_id, username, granted_on, granted_by, revoked_by) VALUES ('User Role', (SELECT role_name FROM user_restr.user_role_link WHERE role_name = @role_to_delete AND sid_id = @user_sid_id_to_delete), @user_sid_id_to_delete, (SELECT username FROM user_restr.sid_list WHERE sid_id = @user_sid_id_to_delete), (SELECT created FROM user_restr.user_role_link WHERE role_name = @role_to_delete AND sid_id = @user_sid_id_to_delete), (SELECT granted_by FROM user_restr.user_role_link WHERE role_name = @role_to_delete AND sid_id = @user_sid_id_to_delete), @username) DELETE FROM user_restr.user_role_link WHERE role_name = @role_to_delete AND sid_id = @user_sid_id_to_delete; -- Write to the privilege log -- -- Write to the 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 ('Role', @role_to_delete, @user_sid_id_to_delete, (SELECT username FROM user_restr.sid_list WHERE sid_id = @user_sid_id_to_delete), 'Revoke', (SELECT sid_id FROM user_restr.sid_list WHERE sid = @connectedusersid), NULL, NULL, @notes, @app_reference) 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 (@recordnotexistmessage <> '') SET @message = @message + ' | ' + @recordnotexistmessage; IF (@nopermissionmessage <> '') SET @message = @message + ' | ' + @nopermissionmessage; END GO