SQL Server DMV script for monitoring USERSTORE_TOKENPERM size by token type and number

  • Article

Here’s a script I was using last week. I wrote this so that it could be used in a custom PSSDIAG / SQLDIAG task, but you can also run it in other automated methods as well.

To include it in SQLDIAG / PSSDIAG I added a custom group section into the XML config file (see previous posts on this blog if you don’t know how to do this), and saved the file in the root of the SQLDIAG directory as exec_userstore1.sql.

 <CustomGroup name="userstore" enabled="true" />
 <CustomTask enabled="true" groupname="userstore" taskname="detailed_scripts" type="TSQL_Script" point="Startup" wait="No" cmd="exec_userstore1.sql"/>

Here’s the script itself:

 /*
 detailed USERSTORE_TOKENPERM analysis for SQL 2005
 script designed to be packaged into PSSDIAG and run over long time frame to monitor
 but can be run individually at a fixed point to capture a snapshot
 
 can be used in association with script number 2 which is verbose and does a snapshot
 of all logins and token in cache, so can be HUGE.
 
 graham kent - microsoft css - 15th September 2009
 */
  
 SET NOCOUNT ON
 SET QUOTED_IDENTIFIER ON
 GO
  
 -- get the token type distribution
  
 WHILE 1=1
 BEGIN
  
 select 'start time:', GETDATE();
  
 SELECT 
 convert(varchar(50),[name]) as 'SOS StoreName', 
 convert(varchar(50),[TokenName]) as 'TokenName',
 convert(varchar(50),[Class]) as 'Class',
 convert(varchar(50),[SubClass]) as 'SubClass',
 count(*) as [Num Entries]
 FROM
 (SELECT name,
 x.value('(//@name)[1]', 'varchar (100)') AS [TokenName],
 x.value('(//@class)[1]', 'varchar (100)') AS [Class],
 x.value('(//@subclass)[1]', 'varchar (100)') AS [SubClass]
 FROM
 (SELECT CAST (entry_data as xml),name
 FROM sys.dm_os_memory_cache_entries
 WHERE type = 'USERSTORE_TOKENPERM') 
 AS R(x,name)
 ) a
 group by a.name,a.TokenName,a.Class,a.SubClass
  
 -- loop on 1 minute basis
 WAITFOR DELAY '00:01:00'
  
 END

When run in this format you’ll get a text file created in the output directory used by SQLDIAG which will contain the results from the script. You can then monitor the individual items in the cache over long periods of time to see if you’re encountering any of the known issues around this security cache.