47 lines
1.6 KiB
Transact-SQL
47 lines
1.6 KiB
Transact-SQL
-- ============================================================
|
|
-- Delete All Users from a Company
|
|
-- Usage: Set @CompanyId to the target company's ID
|
|
-- WARNING: This is a hard delete. Data cannot be recovered.
|
|
-- ============================================================
|
|
|
|
DECLARE @CompanyId INT = 1 -- <-- Change this to the target company ID
|
|
|
|
BEGIN TRANSACTION;
|
|
|
|
BEGIN TRY
|
|
|
|
-- Get the user IDs to be deleted
|
|
DECLARE @UserIds TABLE (Id NVARCHAR(450));
|
|
INSERT INTO @UserIds
|
|
SELECT Id FROM AspNetUsers WHERE CompanyId = @CompanyId;
|
|
|
|
PRINT CONCAT('Users to be deleted: ', (SELECT COUNT(*) FROM @UserIds));
|
|
|
|
-- 1. Delete Identity-related child records first
|
|
DELETE FROM AspNetUserTokens WHERE UserId IN (SELECT Id FROM @UserIds);
|
|
DELETE FROM AspNetUserLogins WHERE UserId IN (SELECT Id FROM @UserIds);
|
|
DELETE FROM AspNetUserClaims WHERE UserId IN (SELECT Id FROM @UserIds);
|
|
DELETE FROM AspNetUserRoles WHERE UserId IN (SELECT Id FROM @UserIds);
|
|
|
|
-- 2. Nullify FK references in business tables (quotes, maintenance records)
|
|
UPDATE Quotes
|
|
SET PreparedById = NULL
|
|
WHERE PreparedById IN (SELECT Id FROM @UserIds);
|
|
|
|
UPDATE MaintenanceRecords
|
|
SET PerformedById = NULL
|
|
WHERE PerformedById IN (SELECT Id FROM @UserIds);
|
|
|
|
-- 3. Delete the users
|
|
DELETE FROM AspNetUsers WHERE Id IN (SELECT Id FROM @UserIds);
|
|
|
|
PRINT 'Users deleted successfully.';
|
|
|
|
COMMIT TRANSACTION;
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
ROLLBACK TRANSACTION;
|
|
PRINT CONCAT('Error: ', ERROR_MESSAGE());
|
|
THROW;
|
|
END CATCH; |