The table in question can have multiple userId but only one record can be active=1 for a user

So put a unique index on userid filtered to active=1,eg

use tempdb

drop table if exists foo

create table foo(userid int, active int)

create unique index ak_only_one_active_row_per_userid on foo(userid) where active = 1
go
insert into foo(userid, active) values (1,1)
insert into foo(userid, active) values (1,1)

The second insert fails with

Msg 2601, Level 14, State 1, Line 10
Cannot insert duplicate key row in object 'dbo.foo' with unique index 'ak_only_one_active_row_per_userid'. The duplicate key value is (1).
The statement has been terminated.

Then you'll both prevent the duplicates from occurring and know who is attempting to insert duplicates because they'll get errors.