none
SqlDependency is not working if table has SQL security policy RRS feed

  • Question

  • I have implemented signalR in my application and tried SqlDependency object to watcher of one of the SQL table. but SqlDependency Change event is not getting fired. I've gone through lots of article but did not get any proper answer and finally I found that table which I am using for watch is having SQL security policy.

    CREATE SECURITY POLICY [Security].[TaskFilter] 
    ADD FILTER PREDICATE [Security].[functionName]([TASK_ID]) ON [dbo].[tableName]
    WITH (STATE = ON, SCHEMABINDING = ON)
    GO

    Finally I got that SqlDependency doesn't work if you have implemented security policy on table.

    Kindly help me for this, appreciate in advance.

    After disabling SQL security policy it works.

    Tuesday, August 6, 2019 5:39 AM

All replies

  • Hi Prashant,

    Thank you for posting here.

    Based on your description, you want to monitor the data in database when you set security policy.

    I find sqldependency has some limitations, you could have a look.

    https://stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, August 6, 2019 9:18 AM
    Moderator
  • thank you Jack for reply 

    Can you please suggest alternative of C# SqlDependancy, we have row level security implemented on table and same table we have to keep watch and once inserted or updated, message should broadcast with new value count on dashboard.

    Regards,

    Prashant

    Tuesday, August 6, 2019 10:04 AM
  • See the following 

    https://www.nuget.org/packages/SqlTableDependency/


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, August 6, 2019 11:23 AM
    Moderator