Forum Discussion

ismail_ozturk's avatar
ismail_ozturk
Icon for Advocate I rankAdvocate I
15 hours ago

Lakehouse SQL Endpoint Sync Issue

Hello all,

I down know it is specific for us or not, but we have a Lakehouse SQL Endpoint Sync Issue. If I delete a table from lakehouse and this table belongs to a Onelake Security role, after deletion SQL Endpoint gives security sync error below. I think if a table is deleted, then it should also be deleted from security role automatically. BTW I dont have DefaultReadWriter role. Dou you have any idea?

Best regards

 

data engineering
lakehouse

1 Reply

  • joeydj's avatar
    joeydj
    Icon for Advocate II rankAdvocate II
    8 hours ago

    ismail_ozturk,

    When a table referenced in a OneLake data access role (OneLake security) is deleted or renamed, the SQL analytics endpoint sync engine encounters a broken reference because the security definition still points to a non-existent object.

    Here is how to resolve the issue and sync the SQL endpoint again:

    Remove the deleted table from the OneLake security role:

    Go to your Lakehouse item.

    Open Manage OneLake data access (preview) or Manage data security.

    Select the security role where the deleted table was assigned permissions.

    Remove or uncheck the deleted/renamed table from the object selection list for that role, then save changes.

    Force a manual refresh of the SQL analytics endpoint metadata:

    Switch to the SQL analytics endpoint view for the Lakehouse.

    On the Home tab of the top ribbon, click Refresh (or execute EXEC sp_refreshsqlmodule if dealing with custom view dependencies).

    Once the orphan table reference is removed from the security role, the automatic sync worker will clear the error and bring the schema back into sync.

    Alternative workaround (if editing the role is blocked):

    Re-create a dummy/empty Delta table with the exact same name as the deleted table in the Lakehouse.

    Once the SQL endpoint successfully syncs, navigate back to the security role settings, safely remove the table reference from the role, and then re-delete the table.