IAM Windows Domain Group inheritance

USE [YOUR_IAM_DATABASE]
GO
/****** Object:  StoredProcedure [dbo].[task_import_active_directory_grp]    Script Date: 27-9-2022 15:45:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[task_import_active_directory_grp]
(
   @tenant_id                      "tenant_id"                      ,
   @usr_grp_id                     "usr_grp_id"                     ,
   @active_directory_grp_name      "active_directory_grp_name"      ,
   @active_directory_domain_name   "active_directory_domain_name"   ,
   @net_bios_domain_name           "net_bios_domain_name"           ,
   @user_name                      "user_name"                      ,
   @password                       "password"                       
)
as
begin

  -- Do not count affected rows for performance
  SET NOCOUNT ON;

    --control_proc_id:      task_import_active_directory_grp
    --template_id:          task_import_active_directory_grp
    --prog_object_item_id:  sf_gui_task_import_active_directory_group
    --template_description: Imports all users from a Active Direcory group in a usr_grp

    declare @query nvarchar(4000)
    declare @path varchar(1000)
    declare @error_msg as varchar(1000)

    begin tran
    begin try

        -- Delete the existing user from the group.
        delete from usr_grp_usr
        where tenant_id  = @tenant_id
          and usr_grp_id = @usr_grp_id

        -- Determine the LDAP path for the group
        set @query = 
        'select @ADsPath = ADsPath 
        from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') +  '''
            SELECT ADsPath 
            FROM ''''LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
            WHERE name =''''' + @active_directory_grp_name + '''''
        '') a'

        exec sp_executesql @query, N'@ADsPath varchar(1000) output',  @ADsPath= @path  output

        if @path is null
        begin
            exec tsf_send_message 'active_directory_grp_not_found', null, 1
            rollback tran
            return
        end

        set @path = substring(@path, len('LDAP://') + 1, len(@path))

        -- Give a different message for the root admin as for another admin
        -- So we don't give up information about users of other tenants 
        if exists( select 1
                     from usr_root_admin 
                    where usr_id = dbo.tsf_user()
                  )
        begin
            set @error_msg = 'tenant_mismatch_for_root_admin'
        end
        else
        begin
            set @error_msg = 'tenant_mismatch_for_non_root_admin'
        end

        -- Check if the user does not exist for another tenant
        --set @query = 
        --'if exists (select 1
        --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
        --    SELECT 
        --        sAMAccountName
        --    FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
        --    WHERE objectCategory=''''Person'''' 
        --        AND objectCategory=''''user'''' 
        --        AND memberof =''''' + @path + '''''
        --        AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        --'') a
        --join usr_general u
        --    on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
        --where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
        --begin
        --    exec tsf_send_message ''' +  @error_msg + ''', null, 0
        --end
        --'
        set @query = 
        'if exists (select 1
        from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
            SELECT 
                sAMAccountName
            FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
            WHERE objectCategory=''''Person'''' 
                AND objectCategory=''''user'''' 
                AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
                AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        '') a
        join usr_general u
            on u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
        where u.tenant_id <> ' + cast(@tenant_id as varchar(10)) + ')
        begin
            exec tsf_send_message ''' +  @error_msg + ''', null, 0
        end
        '

        exec (@query)        


        -- Update users if they exist    
        --set @query = 
        --'update u
        -- set first_name          = a.givenName,
        --     sur_name            = a.sn,
        --     email               = a.mail,
        --     update_user         = dbo.tsf_user(),
        --     update_date_time    = sysdatetime()
        --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
        --    SELECT name
        --    ,sn
        --    ,sAMAccountName
        --    ,givenName, mail
        --    FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
        --    WHERE objectCategory=''''Person'''' 
        --      AND objectCategory=''''user'''' 
        --      AND memberof =''''' + @path + '''''
        --      AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        --'') a
        --join usr_general u
        --  on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
        -- and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
        --where (u.first_name <> a.givenName or u.first_name is null and a.givenName is not null or u.first_name is not null and a.givenName is null
        --    or u.sur_name <> a.sn or u.sur_name is null and a.sn is not null or u.sur_name is not null and a.sn is null
        --    or u.email <> a.mail or u.email is null and a.mail is not null or u.email is not null and a.mail is null)    
        --'
        set @query = 
        'update u
         set first_name          = a.givenName,
             sur_name            = a.sn,
             email               = a.mail,
             update_user         = dbo.tsf_user(),
             update_date_time    = sysdatetime()
        from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
            SELECT name
            ,sn
            ,sAMAccountName
            ,givenName, mail
            FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
            WHERE objectCategory=''''Person'''' 
              AND objectCategory=''''user'''' 
              AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
              AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        '') a
        join usr_general u
          on u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
         and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
        where (u.first_name <> a.givenName or u.first_name is null and a.givenName is not null or u.first_name is not null and a.givenName is null
            or u.sur_name <> a.sn or u.sur_name is null and a.sn is not null or u.sur_name is not null and a.sn is null
            or u.email <> a.mail or u.email is null and a.mail is not null or u.email is not null and a.mail is null)    
        '

        exec (@query)

        -- Determine the users in the group, add the users to IAM if they do not yet exist.
        --set @query = 
        --'insert into usr
        --(
        --    tenant_id,
        --    usr_id,
        --    first_name,
        --    sur_name,
        --    name,
        --    gender,
        --    email,
        --    time_zone_id,
        --    appl_lang_id,
        --    authentication_type,
        --    two_factor_authentication_type,
        --    allow_fallback_to_email,
        --    allow_change_password,        
        --    password_changed_count,
        --    password_forgotten_count,
        --    write_back_up_type_id,        
        --    insert_user,
        --    insert_date_time,
        --    update_user,
        --    update_date_time
        --)
        --select 
        --    ' + cast(@tenant_id as varchar(10)) + ',
        --    ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
        --    a.givenName as first_name,
        --    a.sn as sur_name,   
        --    a.givenName as name,
        --    0, -- Unknown
        --    a.mail as email,
        --    ''Etc/UTC'',
        --    ''ENG'',
        --    1, -- Windows
        --    0, -- Password
        --    0, -- No
        --    0, -- No
        --    0, -- Changed count
        --    0, -- Forgotten count
        --    1, -- None 
        --    dbo.tsf_user(),
        --    sysdatetime(),
        --    dbo.tsf_user(),
        --    sysdatetime()
        --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
        --    SELECT name
        --    ,sn
        --    ,sAMAccountName
        --    ,givenName, mail
        --    FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
        --    WHERE objectCategory=''''Person'''' 
        --      AND objectCategory=''''user'''' 
        --      AND memberof =''''' + @path + '''''
        --      AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        --'') a
        --where not exists (select 1
        --                    from usr u
        --                    -- do not check for tentant, because the usr id must be unique
        --                    where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'

    set @query = 
        'insert into usr
        (
            tenant_id,
            usr_id,
            first_name,
            sur_name,
            name,
            gender,
            email,
            time_zone_id,
            appl_lang_id,
            authentication_type,
            two_factor_authentication_type,
            allow_fallback_to_email,
            allow_change_password,        
            password_changed_count,
            password_forgotten_count,
            write_back_up_type_id,        
            insert_user,
            insert_date_time,
            update_user,
            update_date_time
        )
        select 
            ' + cast(@tenant_id as varchar(10)) + ',
            ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + ' a.sAMAccountName as usr_id,
            a.givenName as first_name,
            a.sn as sur_name,   
            a.givenName as name,
            0, -- Unknown
            a.mail as email,
            ''Etc/UTC'',
            ''ENG'',
            1, -- Windows
            0, -- Password
            0, -- No
            0, -- No
            0, -- Changed count
            0, -- Forgotten count
            1, -- None 
            dbo.tsf_user(),
            sysdatetime(),
            dbo.tsf_user(),
            sysdatetime()
        from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
            SELECT name
            ,sn
            ,sAMAccountName
            ,givenName, mail
            FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
            WHERE objectCategory=''''Person'''' 
              AND objectCategory=''''user'''' 
              AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
              AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        '') a
        where not exists (select 1
                            from usr u
                            -- do not check for tentant, because the usr id must be unique
                            where u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'

        exec (@query)

        -- Add the users to the user group.
        --set @query = 
        --'insert into usr_grp_usr
        --(
        --    tenant_id,
        --    usr_grp_id,
        --    usr_id,
        --    insert_user,
        --    insert_date_time,
        --    update_user,
        --    update_date_time
        --)
        --select 
        --    ' + cast(@tenant_id as varchar(10)) + ',
        --    ''' + @usr_grp_id + ''',
        --    ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
        --    dbo.tsf_user(),
        --    sysdatetime(),
        --    dbo.tsf_user(),
        --    sysdatetime()
        --from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
        --    SELECT name
        --    ,sn
        --    ,sAMAccountName
        --    ,givenName, mail
        --    FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
        --    WHERE objectCategory=''''Person'''' 
        --      AND objectCategory=''''user'''' 
        --      AND memberof =''''' + @path + '''''
        --      AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        --'') a
        --where not exists (select 1
        --                from usr_grp_usr u
        --                where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
        --                and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
        --                and u.usr_grp_id = ''' + @usr_grp_id + ''')
        --  -- Only add users from the same tenant as the user group
        --  and exists (select 1
        --               from usr u
        --               where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
        --                 and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'

     set @query = 
        'insert into usr_grp_usr
        (
            tenant_id,
            usr_grp_id,
            usr_id,
            insert_user,
            insert_date_time,
            update_user,
            update_date_time
        )
        select 
            ' + cast(@tenant_id as varchar(10)) + ',
            ''' + @usr_grp_id + ''',
            ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'sAMAccountName,
            dbo.tsf_user(),
            sysdatetime(),
            dbo.tsf_user(),
            sysdatetime()
        from openrowset(''ADSDSOObject'', ''''' + coalesce('; ''' + @user_name + '''; ''' + @password + ''',', ',') + '''
            SELECT name
            ,sn
            ,sAMAccountName
            ,givenName, mail
            FROM ''''' + 'LDAP://DC=' + replace(@active_directory_domain_name, '.', ',DC=') + ''''' 
            WHERE objectCategory=''''Person'''' 
              AND objectCategory=''''user'''' 
              AND ''''memberof:1.2.840.113556.1.4.1941:''''=''''' + @path + '''''
              AND ''''userAccountControl:1.2.840.113556.1.4.803:''''<>2
        '') a
        where not exists (select 1
                        from usr_grp_usr u
                        where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
                        and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default
                        and u.usr_grp_id = ''' + @usr_grp_id + ''')
          -- Only add users from the same tenant as the user group
          and exists (select 1
                       from usr u
                       where u.tenant_id = ' + cast(@tenant_id as varchar(10)) + '
                         and u.usr_id = ' + coalesce( '''' + @net_bios_domain_name + '\'' + ', '') + 'a.sAMAccountName collate database_default)'

        exec (@query)

        -- When no errors have occurred, update the user group with the new AD information, if needed.
        update usr_grp
        set usr_grp_type = 1,
            active_directory_domain_name = @active_directory_domain_name,
            active_directory_grp_name = @active_directory_grp_name,
            net_bios_domain_name = @net_bios_domain_name,
            active_directory_updated_on = sysdatetime(),
            update_user = dbo.tsf_user(),
            update_date_time = sysdatetime()
        where tenant_id  = @tenant_id
          and usr_grp_id = @usr_grp_id

    end try
    begin catch

        if error_number() = 7321
        begin
            exec tsf_send_message 'active_directory_access_denied', null, 1;
        end
        else
            throw;

        if @@trancount > 0
            rollback transaction;

    end catch;

    if @@trancount > 0
        commit transaction;

end