Geek asks and answers

Permission to access sys.dm_db_index_usage_stats

So, I have a website for internal use that tracks a bunch of statistics for employees. One of them is the lack of any reports. Since the list of items that do not have reports is updated only every couple of days, and there is a 3-day grace period in these reports, I compare Date (releaseDt) that the item was registered as a missing report for the last time the database was updated ( @lastupdate). Thus, if the report database is not updated, but the report is completed, the website will not be regarded by someone due to the lack of a report.

The SQL code works fine with administrator privileges, but for obvious reasons, I am not letting the ASP.NET account have a server administrator level.

I set up an SQL account that ASP.NET C # code uses to log in, and permissions for everything else are fine. (Read access only to the specific databases that he uses.) I cannot understand what he needs to get in order to gain access to reading this particular view of dynamic control.

Would thank the suggestions using either Management Studio or using the GRANTSQL statement.

This related information seems to have relevant information:

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
+5
source share
1 answer

You cannot provide SELECTon a DMV, as this is prevented by the policy for a DMV with a server:

GRANT SELECT ON sys.dm_db_index_usage_stats TO peon;

Results in:

Msg 4629, 16, 10, 1
, .

BOL, , , VIEW SERVER STATE. :

USE master;
GO
GRANT VIEW SERVER STATE TO peon;
-- if it a Windows login then:
GRANT VIEW SERVER STATE TO [Domain\peon];

. , - , ASP.NET, , , , .

+11

More articles:


All Articles