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.