July 16, 2013 at 2:08 pm
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?
July 16, 2013 at 4:23 pm
Put the statement in a stored procedure or user-defined functon. Sign the module with a certificate in master. Create login from the certificate and grant that login VIEW SERVER STATE. Grant permission to NETWORK SERVICE to the stored procedure.
For more details on certificate signing, see this article on my web site: http://www.sommarskog.se/grantperm.html
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 18, 2013 at 5:56 pm
Erland Sommarskog (7/16/2013)
Put the statement in a stored procedure or user-defined functon. Sign the module with a certificate in master. Create login from the certificate and grant that login VIEW SERVER STATE. Grant permission to NETWORK SERVICE to the stored procedure.
Thanks for the reply! I figured out how to create a login from a certificate and how to grant it VIEW SERVER STATE. I am not sure about the following:
1) What do you mean by "Sign the module?" I looked for a way to sign the stored procedure, but couldn't find a way to do it.
2) How does this connect to the network service user? It seems like maybe I should have the stored procedure run as the user created from the certificate?
July 19, 2013 at 1:59 am
ericjorg (7/18/2013)
1) What do you mean by "Sign the module?" I looked for a way to sign the stored procedure, but couldn't find a way to do it.
ADD SIGNATURE. Did you read the article I referred you to?
2) How does this connect to the network service user? It seems like maybe I should have the stored procedure run as the user created from the certificate?
GRANT EXECUTE on the procedure to the user in question.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 29, 2013 at 3:09 pm
I ended up solving this problem by using the below query instead. Even though access to the system views appears to be the same, my network service account does not have permissions issues.
SELECT TOP 1 LASTUPDATED FROM (
SELECT CONCAT(S.name, '.', B.Name) AS FullName, MAX(STATS_DATE (ID,INDID)) AS LASTUPDATED
FROM SYS.SYSINDEXES AS A
INNER JOIN SYS.OBJECTS AS B ON A.ID = B.OBJECT_ID
INNER JOIN SYS.SCHEMAS AS S ON s.schema_id = b.schema_id
WHERE B.TYPE = 'U' AND STATS_DATE (ID,INDID) IS NOT NULL
GROUP BY B.Name, S.name ) AS Q
WHERE (FullName = @Table_0)) ORDER BY LASTUPDATED DESC
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply