I'm running an asp.net MVC application that uses signalr,and I'm running signalR off of the SQL server backplane ( https://learn.microsoft.com/en-us/aspnet/signalr/overview/performance/scaleout-with-sql-server ). I was having some issues with this setup earlier, and one of my developers deleted the 'aspstate' database and re-ran aspnet_regsql to 'clear things out' in his mind. Unfortunately that seems to have mucked things up even further, and I don't know how to fix it. Now i'm getting the following error:
The EXECUTE permission was denied on the object 'sp_executesql', database 'mssqlsystemresource', schema 'sys'. Violation of PRIMARY KEY constraint 'PK__Schema__04D490DBAD491E7F'. Cannot insert duplicate key in object 'SignalR.Schema'. The duplicate key value is (1). Installing SignalR SQL objects Database schema [SignalR] already exists Table [SignalR].[Schema] already exists Installing schema version 1 Mesage table [SignalR].[Messages_0] already exists Message ID table [SignalR].[Messages_0_Id] alread exists The statement has been terminated. Schema version 1 installed SignalR SQL objects installed
I've tried deleting this database and pointing it to a new one, but no luck at all, it feels like something is cached and nothing i do will clear it out. Are there any ideas how to approach this? Been beating my head at this for a while, and no amount of reboots/resets/rebuilds has fixed it.
so apparently the answer ended up being that the DB admin hadn't granted the user that accessed the signalR databases execute permission. Says so right in the error, but i just assumed it was there. Oh well!