Xtraction Settings error: CREATE FUNCTION permission denied in database

Version 3

    Environment:

    Xtraction 2016.2

     

    Requires Access To:

    SQL Server Management Studio

     

    Error message:

    An exception has occurred while executing a Transact-SQL statement or batch.

     

    Problem:

    When running the Xtraction settings utility in 2016.2 for the first time, either after an upgrade or new install, an error is returned after pressing OK.

     

    Cause:

    As of Xtraction 2016.2 the settings utility has been updated to add on the xtr_folder_lineage Function to the database automatically if it does not exist. If the Xtraction SQL user does not have the appropriate permissions to create a function the utility will return an error.

     

    Solution / Workaround:

    1. In your SQL instance modify the Xtraction user's mapping to include db_owner for the Xtraction database only. This change will grant the user the necessary privilege to create the missing function.

    2. After making the change re-open the Xtraction Settings utility and press the OK button again.

    3. At this point the settings utility will will attempt to perform the Create Function action again and with the ownership privilege it will be able to do so.

     

    Additional documentation on the xtr_folder_lineage function can be found here: https://community.landesk.com/docs/DOC-41430

    *Note: This will more commonly be seen after an upgrade to due the change in the installation documentation requiring the Xtraction database user to now include the ownership privilege. Before it only needed the db_reader and db_writer privileges.