mirror of
https://github.com/bitwarden/server
synced 2026-01-31 08:43:19 +00:00
ad19efcff7
* Exclude invited users from claimed domain checks. These users should be excluded by the JOIN on UserId, but it's a known issue that some invited users have this FK set.
30 lines
834 B
Transact-SQL
30 lines
834 B
Transact-SQL
CREATE OR ALTER PROCEDURE [dbo].[OrganizationUser_ReadByOrganizationIdWithClaimedDomains_V2]
|
|
@OrganizationId UNIQUEIDENTIFIER
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
WITH OrgUsers AS (
|
|
SELECT *
|
|
FROM [dbo].[OrganizationUserView]
|
|
WHERE [OrganizationId] = @OrganizationId
|
|
AND [Status] != 0 -- Exclude invited users
|
|
),
|
|
UserDomains AS (
|
|
SELECT U.[Id], U.[EmailDomain]
|
|
FROM [dbo].[UserEmailDomainView] U
|
|
WHERE EXISTS (
|
|
SELECT 1
|
|
FROM [dbo].[OrganizationDomainView] OD
|
|
WHERE OD.[OrganizationId] = @OrganizationId
|
|
AND OD.[VerifiedDate] IS NOT NULL
|
|
AND OD.[DomainName] = U.[EmailDomain]
|
|
)
|
|
)
|
|
SELECT OU.*
|
|
FROM OrgUsers OU
|
|
JOIN UserDomains UD ON OU.[UserId] = UD.[Id]
|
|
OPTION (RECOMPILE);
|
|
END
|
|
GO
|