Permission to access sys.dm_db_index_usage_stats

Viewed 11805

So I have a website for internal use which tracks a bunch of stats for employees. One of these is whether they are missing any reports. Since the list of items that don't have reports yet only gets updated every couple days, and there is a 3 day grace period on these reports, I am comparing the Date (releaseDt) that the item was logged as missing a report to the last time the database was updated (@lastupdate). This way, if the reports database hasn't updated but the report is complete, the website isn't ratting someone out for missing a report.

The SQL code works fine with admin level privledges, but for obvious reasons I'm not letting the ASP.NET account have server admin level.

I have set up a SQL account that the ASP.NET C# code uses to log in, and the permissions for everything else are fine. (Read access only for the specific databases it uses.) I can't figure out what to give it access to in order to have access to read this specific dynamic management view.

Would appreciate suggestions using either Management Studio, or using a GRANT SQL statement.

This appears to have related information on the view in question:

sys.dm_db_index_usage_stats (Transact-SQL)

DECLARE @lastupdate datetime
     SELECT @lastupdate = last_user_update from sys.dm_db_index_usage_stats
     WHERE OBJECT_ID = OBJECT_ID('MissingReport')

SELECT 
    COALESCE(Creator, 'Total') AS 'Creator',
    COUNT(*) AS Number, 
    '$' + CONVERT(varchar(32), SUM(Cost), 1) AS 'Cost' 
    FROM MissingReport 
    WHERE NOT( 
        [bunch of conditions that make something exempt from needing a report]
        OR
        (
            DATEDIFF(day,ReleaseDt,@lastupdate) <= 3
        )
    )
    GROUP BY Creator WITH ROLLUP
1 Answers
Related