SQL Server - get last updated time for table

Question

I need to know when the data in a table was last modified (data inserted, created, deleted). The common answer is to extract the data from sys.dm_db_index_usage_stats, but I don't have access to that table and as I understand it, access to that is a server-level permission, and since this is on a corporate shared server, the odds of the IT department granting me access is up there with pigs flying.

Is there a way to get this information, that does not require greatly elevated privileges?

---

Update #1

Some additional information: What I am doing, is caching the contents of this table locally, and need to know when to update my local cache. This means:

1. I don't need an actual timestamp, just any value that I can compare against a locally-cached value that tells me "things have changed, update the cache"
2. If this value changes too often (e.g. gets reset every time they restart the server which is extremely rarely) it's OK, it just means I do an extra cache update that I didn't actually need to do

---

Update #2

I should have done this early on, but I just assumed I would be able to create tables as needed ... but I'm not. Permission not granted. So neither of the proposed methods will work :-(

Here's my new thought : I can call

select checksum_agg(binary_checksum(*))

and get a checksum on the entire remote table. Then, if the checksum changes, I know the table has changed and I can update my local cache. I've seen the problems with checksum, but using HASHBYTES sounds like it would be much more complicated and much slower.

This is working, the problem is that when the checksum changes, I have to reload the entire cache. My table has so many rows that returning the checksum per row takes an unacceptably long time, is there a way to use the "OVER" clause and get maybe 10 checksums, the first checksum for the first tenth of the rows, etc.?

Answer 1

If you can modify their schema, then you should be able to add a trigger.

Once you add a lastmodified column, you can use this trigger to get the time updated any time the record changes:

CREATE trigger [dbo].[TR_TheirTable_Timestamp]
on [dbo].[TheirTable] for update
as 
begin
    update
        dbo.TheirTable
    set
        lastmodified=getdate()
    from
        Inserted
    where
        dbo.TheirTable.UniqueID=Inserted.UniqueKey
end

The reason I do it only for update, and not insert is because I can see a new record, and I don't need a timestamp, but a modified record I can compare the time I last updated the record. if you want an insert update, then

on [dbo].[TheirTable] for insert,update

would work

If you just wanted to know when the table was updated, then the trigger could write to another table with the tablename and date, and you wouldn't have to modify their schema