Beware of dynamic SQL as exec'ing a delete statement in a sproc for example will still require delete permissions on the base table.

Probably an impossible suggestion due to the 3rd party application but just in case:

If all of the updates are performed through stored procedures you could revoke update and delete permissions...

You should be able to see every line that is executed in a stored procedure if you have all of the stored procedure events turned on when creating the trace.

It...

I need a temp table because the actual query is hugely complicated and runs as 4 parts (to avoid outer join / unique issues).  I realise I could theoretically use derived...

Slight misunderstanding there.

My current fix (which worked) was to create a table inside the sproc with the @@spid as part of the table name so it should be treated exactly...

Thanks, it looks like youve hit the nail on the head already!

Ive just (as in just before you posted) executed the query with a non temporary table and it takes 2 seconds.

I...

If you have to implement your own login, which is how anonymous authentication works for web apps, we normally:

1. Login to the database with a single SQL login (dont use...

The best option is to persuade the powers that be that the user logged into windows is the one that connects to the app so that you can use windows...