Automatic duplicate checking when creating a new contact record does not work due when the underlying SQL user has only public, db_datawriter and db_datareader rights

Version 1

    Details

    Automatic duplicate checking when creating a new contact record does not work due when the underlying SQL user has only public, db_datawriter and db_datareader rights

    [PRE-REQUISITES]

    A. Have your dbalias and usual access setup  with SQL sa user all users - this is very often the default setup and  should not require any specific adjustments >> you may verify in  GoldMine >> Help >> About GoldMine >> System >>  Logged User [SQL Login: sa]
    B. Additionally setup a new generic SQL User for GoldMine (restricted user) - following are steps in MS SQL 2014
    1. SQL Server Management Studio
    2. Browse to Security >> Logins
    3. Right Click >> New Login
    4. On tab General (left pane)
    4.a. Login Name = gmtestuser
    4.b. SQL Authentication
    4.c. Provide a password
    4.d. Uncheck Enforce password expiration + uncheck User must change password at next login
    4.e. Default Database = Select the GoldMine database


    5. Switch to tab Server Roles (left pane)
    5.a. Make sure that only public is activated


    6. Switch to tab User Mapping
    6.a. Map GoldMine database (Upper pane)
    6.b. Database Role membership for GoldMine database = db_datareader + db_datawriter + public


    7. No further changes necessary >> OK
    8. Browse to your GoldMine database >> Security >> Users >> there should be now a gmtestuser available



    [STEPS TO PREPARE]
    1. Within GoldMine >> Login as MASTER
    2. Tools >> User Settings
    3. New
    3.a. Username = TEST1, Full Name = Test1, password = access, no master rights >> OK
    4. New
    4.a. Username = TEST2, Full Name = Test2, password = access, no master rights
    4.b. Tab Access
    4.c. SQL Logon name = gmtestuser, SQL Password = provide the proper password set on Prerequisits >> Step 4.c. >> OK


    5. Close
    6. Exit GoldMine

    [STEPS TO REPRODUCE]
    1. Login as TEST1
    2. Help >> About GoldMine >> System >> Logged User: TEST1 [SQL Login: sa]

    3. OK
    4. Browse to Art Bardtoll
    5. New Record button
    6. Check Copy current record >> make sure that Phone1 is filled with Art's Phone number
    7. Uncheck Copy current record >> make sure that Phone1 is still filled with Art's Phone number
    8. OK
    >> RESULT 1:
    As expected a Warning: duplicate account creation window appears



    9. After the Warning duplicate account creation dialog appears >> select Add Anyway
    10. Logout of GoldMine
    11. Login into GoldMine now as TEST2
    12. Help >> About GoldMine >> System >> Logged User: TEST2 [SQL Login: gmtestuser]
    13. OK
    14. Browse to Art Bardtoll
    15. New Record button
    16. Check Copy current record >> make sure that Phone1 is filled with Art's Phone number
    17. Uncheck Copy current record >> make sure that Phone1 is still filled with Art's Phone number
    18. OK
    >> RESULT:
    NOTHING HAPPENS - Although we know that there is a duplicate (now even at least 2) the record is simply added into the database although  the phone number already exists and the Record Creation window indicates  that Phone number is checked for duplicate


    Resolution

    [CAUSE]
    - When GoldMine Premium Edition checks for a duplicate record during a contact record creation a temporary table will be created - tmpXXXXXUSERNAMExxx for example tmpZAXH2J4YQMASTER116 which will be deleted after the contact record creation again




    - As the current SQL user has only db_datawriter and db_datareader rights the table cannot be created and GoldMine cannot make a duplicate check
    >> Therefore the specific SQL User additionally needs Create and delete a table rights and also Alter Schema rights.
    -  This is RM #204638

    [RESOLUTION]
    A. Provide the SQL Login db_owner rights on the GoldMine database
    - GoldMine Technical Support always recommends that the used SQL user for GoldMine has db_owner rights on the GoldMine database.

    1. SQL Server Management Studio
    2. Browse to Security >> Logins
    3. Right Click on gmtestuser (the specific SQL login) >> Properties
    4. Switch to tab User Mapping
    4.a. Highlight the already mapped GoldMine database (Upper pane)
    4.b. Add db_owner in the Database Role membership for the GoldMine database


    5. No further changes necessary >> OK
    6. Verify the behavior again in GoldMine >> now the duplicate check works also for this SQL login as expected


    B. Provide the SQL Login explicit necessary rights on the GoldMine database
    - Providing further explicit rights requires Microsoft SQL DBA knowledge and GoldMine Technical Support will not be able to assist or troubleshoot any assignment of further explicit rights, while GoldMine Technical Support recommends that the used SQL user for GoldMine has db_owner rights on the GoldMine database for this specific option also the database role membership db_ddladmin should work.

    - Further information which is provided by GoldMine Technical Support only as-is and without any warranty of any kind can be found on the below threads. Please keep in mind that any non GoldMine or non Heat Software links are only provided as-is and content and availability may change at any time without any notice
    Create Table rights - http://msdn.microsoft.com/en-us/library/ms178569.aspx
    Alter Schema rights - http://msdn.microsoft.com/en-us/library/ms173423.aspx


    1. SQL Server Management Studio
    2. Browse to Security >> Logins
    3. Right Click on gmtestuser (the specific SQL login) >> Properties
    4. Switch to tab User Mapping
    4.a. Highlight the already mapped GoldMine database (Upper pane)
    4.b. Add db_ddladmin in the Database Role membership for the GoldMine database


    5. No further changes necessary >> OK
    6. Verify the behavior again in GoldMine >> now the duplicate check works also for this SQL login as expected


    Additional Notes:
    usually GoldMine users should have CREATE TABLE and ALTER SCHEMA permissions in SQL as well as SELECT, INSERT, UPDATE and DELETE permissions on the GoldMine database and the related tables.