Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

NETWORK SERVICE cant read system view Expand / Collapse
Author
Message
Posted Tuesday, July 16, 2013 2:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 4:35 PM
Points: 13, Visits: 32
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?
Post #1474282
Posted Tuesday, July 16, 2013 4:23 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
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


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1474332
Posted Thursday, July 18, 2013 5:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 4:35 PM
Points: 13, Visits: 32
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?
Post #1475239
Posted Friday, July 19, 2013 1:59 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 2:28 PM
Points: 786, Visits: 691
ericjorg (7/18/2013)
[quote]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.


Erland Sommarskog, SQL Server MVP, www.sommarskog.se
Post #1475311
Posted Monday, July 29, 2013 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 4:35 PM
Points: 13, Visits: 32
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

Post #1478755
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse