I’m trying to run the following query from a Web Service:
SELECT last_user_update FROM sys.dm_db_index_usage_stats
I get an error when I try this, saying that the current user does not have permissions. Here’s what I know;
- The web service runs as NT AUTHORITY\NETWORK SERVICE
- NT AUTHORITY\NETWORK SERVICE has the “public” role on the database
- The view sys.dm_db_index_usage_stats has two SELECT permission options, one with a blank grantor and one with “dbo” as a grantor. “public” is given access to the one with dbo as the grantor
- I tried to check the other select box, but SQL quietly unchecks it when I close the window, so I'm basically not able to change the permissions on this view.
Is there a way that I can grant access to sys.dm_db_index_usage_stats for NT AUTHORITY\NETWORK SERVICE?
or... Is there another way I can discover the last access time on a table that does not require access to sys.dm_db_index_usage_stats?