Archive

Posts Tagged ‘Enable display name search’

SharePoint 2010 FBA: Enable search by part or full name in People Picker

March 18, 2012 4 comments

 
Please refer to this post of mine on how to configure Forms Based Authentication (FBA) on a SharePoint Web Application using ASP.NET SQL Membership Provider.

After you setup FBA the People Picker control by default will only search for user’s by using the username. However, if you would like to search for users by part or full display name then you will need to carry out the following changes to the Membership Database:

  • Add a column “ProfileNames” of type nvarchar(255) in the table: aspnet_Users
  • Update the stored procedure: aspnet_Membership_FindUsersByName by replacing the following where clause:
    WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch) 
    

    with:
     

    WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.ProfileNames LIKE '%' + LOWER(@UserNameToMatch) + '%'
    
  • Create the following Trigger on the “aspnet_Profile” table:
    CREATE TRIGGER [dbo].[ProfileProperty_Trigger] 
       ON   [dbo].[aspnet_Profile] 
       AFTER INSERT,UPDATE
    AS 
    BEGIN
    SET NOCOUNT ON;
    DECLARE @Names nvarchar(50)
    DECLARE @UID nvarchar(50)
    
    SELECT 
    @Names = p.PropertyValuesString,
    @UID = p.UserId
    FROM aspnet_Profile p
    INNER JOIN inserted i
    ON p.UserId = i.UserId
    
    UPDATE aspnet_Users SET ProfileNames = @Names WHERE aspnet_Users.UserId = @UID
    END
    
    •  
      That is it, after making these changes the People Picker control should now match by username as well as display name.