I get this question asked very often and sometimes have to dig up my notes on the subject, so I figured I would just post the SQL script that I use for this.
Smartlist favorites are traditionally stored in the ASIEXP81 table on a user-company basis. It’s important to keep this in mind, as you can tailor this script to copy favorites from one user to another in the same company or the same user across various companies. However, this script replicates all favorites, across all companies for one user to another user.
USE DYNAMICS;
GO
-- remove any records for the destination user prior to synchronizing
DELETE FROM ASIEXP81 WHERE USERID = '<DestinationUserID>';
-- execute synchronization
INSERT INTO ASIEXP81
(ASI_Favorite_Dict_ID
,ASI_Favorite_Type
,ASI_Favorite_Save_Level
,CMPANYID
,USRCLASS
,USERID
,ASI_Favorite_Name
,ASI_Field_Dict_ID_1
,ASI_Field_Dict_ID_2
,ASI_Field_Dict_ID_3
,ASI_Field_Dict_ID_4
,ASI_Field_List_1
,ASI_Field_List_2
,ASI_Field_List_3
,ASI_Field_List_4
,ASI_Search_Type_1
,ASI_Search_Type_2
,ASI_Search_Type_3
,ASI_Search_Type_4
,ASI_Match_Case_1
,ASI_Match_Case_2
,ASI_Match_Case_3
,ASI_Match_Case_4
,ASI_Start_Account_Number_1_1
,ASI_Start_Account_Number_1_2
,ASI_Start_Account_Number_1_3
,ASI_Start_Account_Number_1_4
,ASI_Start_Account_Number_1_5
,ASI_Start_Account_Number_1_6
,ASI_Start_Account_Number_1_7
,ASI_Start_Account_Number_1_8
,ASI_Start_Account_Number_1_9
,ASI_Start_Account_Number_1_10
,ASI_Start_Account_Number_2_1
,ASI_Start_Account_Number_2_2
,ASI_Start_Account_Number_2_3
,ASI_Start_Account_Number_2_4
,ASI_Start_Account_Number_2_5
,ASI_Start_Account_Number_2_6
,ASI_Start_Account_Number_2_7
,ASI_Start_Account_Number_2_8
,ASI_Start_Account_Number_2_9
,ASI_Start_Account_Number_2_10
,ASI_Start_Account_Number_3_1
,ASI_Start_Account_Number_3_2
,ASI_Start_Account_Number_3_3
,ASI_Start_Account_Number_3_4
,ASI_Start_Account_Number_3_5
,ASI_Start_Account_Number_3_6
,ASI_Start_Account_Number_3_7
,ASI_Start_Account_Number_3_8
,ASI_Start_Account_Number_3_9
,ASI_Start_Account_Number_3_10
,ASI_Start_Account_Number_4_1
,ASI_Start_Account_Number_4_2
,ASI_Start_Account_Number_4_3
,ASI_Start_Account_Number_4_4
,ASI_Start_Account_Number_4_5
,ASI_Start_Account_Number_4_6
,ASI_Start_Account_Number_4_7
,ASI_Start_Account_Number_4_8
,ASI_Start_Account_Number_4_9
,ASI_Start_Account_Number_4_10
,ASI_Start_Date_Token_DDL_1
,ASI_Start_Date_Token_DDL_2
,ASI_Start_Date_Token_DDL_3
,ASI_Start_Date_Token_DDL_4
,ASI_Start_Date_1
,ASI_Start_Date_2
,ASI_Start_Date_3
,ASI_Start_Date_4
,ASI_Start_DDL_1
,ASI_Start_DDL_2
,ASI_Start_DDL_3
,ASI_Start_DDL_4
,ASI_String_Start_1
,ASI_String_Start_2
,ASI_String_Start_3
,ASI_String_Start_4
,ASI_End_Account_Number_1_1
,ASI_End_Account_Number_1_2
,ASI_End_Account_Number_1_3
,ASI_End_Account_Number_1_4
,ASI_End_Account_Number_1_5
,ASI_End_Account_Number_1_6
,ASI_End_Account_Number_1_7
,ASI_End_Account_Number_1_8
,ASI_End_Account_Number_1_9
,ASI_End_Account_Number_1_10
,ASI_End_Account_Number_2_1
,ASI_End_Account_Number_2_2
,ASI_End_Account_Number_2_3
,ASI_End_Account_Number_2_4
,ASI_End_Account_Number_2_5
,ASI_End_Account_Number_2_6
,ASI_End_Account_Number_2_7
,ASI_End_Account_Number_2_8
,ASI_End_Account_Number_2_9
,ASI_End_Account_Number_2_10
,ASI_End_Account_Number_3_1
,ASI_End_Account_Number_3_2
,ASI_End_Account_Number_3_3
,ASI_End_Account_Number_3_4
,ASI_End_Account_Number_3_5
,ASI_End_Account_Number_3_6
,ASI_End_Account_Number_3_7
,ASI_End_Account_Number_3_8
,ASI_End_Account_Number_3_9
,ASI_End_Account_Number_3_10
,ASI_End_Account_Number_4_1
,ASI_End_Account_Number_4_2
,ASI_End_Account_Number_4_3
,ASI_End_Account_Number_4_4
,ASI_End_Account_Number_4_5
,ASI_End_Account_Number_4_6
,ASI_End_Account_Number_4_7
,ASI_End_Account_Number_4_8
,ASI_End_Account_Number_4_9
,ASI_End_Account_Number_4_10
,ASI_End_Date_Token_DDL_1
,ASI_End_Date_Token_DDL_2
,ASI_End_Date_Token_DDL_3
,ASI_End_Date_Token_DDL_4
,ASI_End_Date_1
,ASI_End_Date_2
,ASI_End_Date_3
,ASI_End_Date_4
,ASI_End_DDL_1
,ASI_End_DDL_2
,ASI_End_DDL_3
,ASI_End_DDL_4
,ASI_String_End_1
,ASI_String_End_2
,ASI_String_End_3
,ASI_String_End_4
,ASI_Search_From_Str_1
,ASI_Search_From_Str_2
,ASI_Search_From_Str_3
,ASI_Search_From_Str_4
,ASI_Search_To_Str_1
,ASI_Search_To_Str_2
,ASI_Search_To_Str_3
,ASI_Search_To_Str_4
,ASI_Search_Logic_Type
,ASI_Max_Records
,ASI_Sort_Dict_ID
,ASI_Sort_Field
,ASI_Sort_Type
,ASI_Field_Comparison_1
,ASI_Field_Comparison_2
,ASI_Field_Comparison_3
,ASI_Field_Comparison_4
,ASI_Start_Comp_Field_ID_1
,ASI_Start_Comp_Field_ID_2
,ASI_Start_Comp_Field_ID_3
,ASI_Start_Comp_Field_ID_4
,ASI_Start_Comp_Field_Dic_1
,ASI_Start_Comp_Field_Dic_2
,ASI_Start_Comp_Field_Dic_3
,ASI_Start_Comp_Field_Dic_4
,ASI_End_Comp_Field_ID_1
,ASI_End_Comp_Field_ID_2
,ASI_End_Comp_Field_ID_3
,ASI_End_Comp_Field_ID_4
,ASI_End_Comp_Field_Dict_1
,ASI_End_Comp_Field_Dict_2
,ASI_End_Comp_Field_Dict_3
,ASI_End_Comp_Field_Dict_4)
SELECT ASI_Favorite_Dict_ID
,ASI_Favorite_Type
,ASI_Favorite_Save_Level
,CMPANYID
,USRCLASS
,'<DestinationUserID>'
,ASI_Favorite_Name
,ASI_Field_Dict_ID_1
,ASI_Field_Dict_ID_2
,ASI_Field_Dict_ID_3
,ASI_Field_Dict_ID_4
,ASI_Field_List_1
,ASI_Field_List_2
,ASI_Field_List_3
,ASI_Field_List_4
,ASI_Search_Type_1
,ASI_Search_Type_2
,ASI_Search_Type_3
,ASI_Search_Type_4
,ASI_Match_Case_1
,ASI_Match_Case_2
,ASI_Match_Case_3
,ASI_Match_Case_4
,ASI_Start_Account_Number_1_1
,ASI_Start_Account_Number_1_2
,ASI_Start_Account_Number_1_3
,ASI_Start_Account_Number_1_4
,ASI_Start_Account_Number_1_5
,ASI_Start_Account_Number_1_6
,ASI_Start_Account_Number_1_7
,ASI_Start_Account_Number_1_8
,ASI_Start_Account_Number_1_9
,ASI_Start_Account_Number_1_10
,ASI_Start_Account_Number_2_1
,ASI_Start_Account_Number_2_2
,ASI_Start_Account_Number_2_3
,ASI_Start_Account_Number_2_4
,ASI_Start_Account_Number_2_5
,ASI_Start_Account_Number_2_6
,ASI_Start_Account_Number_2_7
,ASI_Start_Account_Number_2_8
,ASI_Start_Account_Number_2_9
,ASI_Start_Account_Number_2_10
,ASI_Start_Account_Number_3_1
,ASI_Start_Account_Number_3_2
,ASI_Start_Account_Number_3_3
,ASI_Start_Account_Number_3_4
,ASI_Start_Account_Number_3_5
,ASI_Start_Account_Number_3_6
,ASI_Start_Account_Number_3_7
,ASI_Start_Account_Number_3_8
,ASI_Start_Account_Number_3_9
,ASI_Start_Account_Number_3_10
,ASI_Start_Account_Number_4_1
,ASI_Start_Account_Number_4_2
,ASI_Start_Account_Number_4_3
,ASI_Start_Account_Number_4_4
,ASI_Start_Account_Number_4_5
,ASI_Start_Account_Number_4_6
,ASI_Start_Account_Number_4_7
,ASI_Start_Account_Number_4_8
,ASI_Start_Account_Number_4_9
,ASI_Start_Account_Number_4_10
,ASI_Start_Date_Token_DDL_1
,ASI_Start_Date_Token_DDL_2
,ASI_Start_Date_Token_DDL_3
,ASI_Start_Date_Token_DDL_4
,ASI_Start_Date_1
,ASI_Start_Date_2
,ASI_Start_Date_3
,ASI_Start_Date_4
,ASI_Start_DDL_1
,ASI_Start_DDL_2
,ASI_Start_DDL_3
,ASI_Start_DDL_4
,ASI_String_Start_1
,ASI_String_Start_2
,ASI_String_Start_3
,ASI_String_Start_4
,ASI_End_Account_Number_1_1
,ASI_End_Account_Number_1_2
,ASI_End_Account_Number_1_3
,ASI_End_Account_Number_1_4
,ASI_End_Account_Number_1_5
,ASI_End_Account_Number_1_6
,ASI_End_Account_Number_1_7
,ASI_End_Account_Number_1_8
,ASI_End_Account_Number_1_9
,ASI_End_Account_Number_1_10
,ASI_End_Account_Number_2_1
,ASI_End_Account_Number_2_2
,ASI_End_Account_Number_2_3
,ASI_End_Account_Number_2_4
,ASI_End_Account_Number_2_5
,ASI_End_Account_Number_2_6
,ASI_End_Account_Number_2_7
,ASI_End_Account_Number_2_8
,ASI_End_Account_Number_2_9
,ASI_End_Account_Number_2_10
,ASI_End_Account_Number_3_1
,ASI_End_Account_Number_3_2
,ASI_End_Account_Number_3_3
,ASI_End_Account_Number_3_4
,ASI_End_Account_Number_3_5
,ASI_End_Account_Number_3_6
,ASI_End_Account_Number_3_7
,ASI_End_Account_Number_3_8
,ASI_End_Account_Number_3_9
,ASI_End_Account_Number_3_10
,ASI_End_Account_Number_4_1
,ASI_End_Account_Number_4_2
,ASI_End_Account_Number_4_3
,ASI_End_Account_Number_4_4
,ASI_End_Account_Number_4_5
,ASI_End_Account_Number_4_6
,ASI_End_Account_Number_4_7
,ASI_End_Account_Number_4_8
,ASI_End_Account_Number_4_9
,ASI_End_Account_Number_4_10
,ASI_End_Date_Token_DDL_1
,ASI_End_Date_Token_DDL_2
,ASI_End_Date_Token_DDL_3
,ASI_End_Date_Token_DDL_4
,ASI_End_Date_1
,ASI_End_Date_2
,ASI_End_Date_3
,ASI_End_Date_4
,ASI_End_DDL_1
,ASI_End_DDL_2
,ASI_End_DDL_3
,ASI_End_DDL_4
,ASI_String_End_1
,ASI_String_End_2
,ASI_String_End_3
,ASI_String_End_4
,ASI_Search_From_Str_1
,ASI_Search_From_Str_2
,ASI_Search_From_Str_3
,ASI_Search_From_Str_4
,ASI_Search_To_Str_1
,ASI_Search_To_Str_2
,ASI_Search_To_Str_3
,ASI_Search_To_Str_4
,ASI_Search_Logic_Type
,ASI_Max_Records
,ASI_Sort_Dict_ID
,ASI_Sort_Field
,ASI_Sort_Type
,ASI_Field_Comparison_1
,ASI_Field_Comparison_2
,ASI_Field_Comparison_3
,ASI_Field_Comparison_4
,ASI_Start_Comp_Field_ID_1
,ASI_Start_Comp_Field_ID_2
,ASI_Start_Comp_Field_ID_3
,ASI_Start_Comp_Field_ID_4
,ASI_Start_Comp_Field_Dic_1
,ASI_Start_Comp_Field_Dic_2
,ASI_Start_Comp_Field_Dic_3
,ASI_Start_Comp_Field_Dic_4
,ASI_End_Comp_Field_ID_1
,ASI_End_Comp_Field_ID_2
,ASI_End_Comp_Field_ID_3
,ASI_End_Comp_Field_ID_4
,ASI_End_Comp_Field_Dict_1
,ASI_End_Comp_Field_Dict_2
,ASI_End_Comp_Field_Dict_3
,ASI_End_Comp_Field_Dict_4
FROM ASIEXP81 WHERE USERID = '<SourceUserID>';
GO
In the above script <SourceUserID> and <DestinationUserID> are placeholders for the actual user IDs.