Community
Loading...
Posted inSQL
Microsoft
5 years ago

TokenAndPermUserStore grows for each sp_setapprole or EXECUTE AS USER

spsetapprole & spunsetapprole or EXECUTE AS & REVERT cause TokenAndPermUserStore to grow to 2GB and performance to degrade when run in a loop (simulating a connection pool that performs these when getting or returning a connection)
Declined
SecurityBugs
Company Response
Jordan Hays
Company Response
1 year ago

1 Comment

You must to comment
Erland Somnmarskog3 years ago
0 Reports0
Here is a repro: SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type = 'USERSTORE_TOKENPERM' AND name = 'TokenAndPermUserStore' CREATE APPLICATION ROLE rolle WITH PASSWORD = 'EveryoneKnowsThis!' go DECLARE @cookie varbinary(8000) EXEC sp_setapprole N'rolle', N'EveryoneKnowsThis!', @fCreateCookie = 'true', @cookie = @cookie OUTPUT EXEC sp_unsetapprole @cookie = @cookie go 1000 SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type = 'USERSTORE_TOKENPERM' AND name = 'TokenAndPermUserStore' go DROP APPLICATION ROLE rolle go CREATE USER nissenils WITHOUT LOGIN go DECLARE @cookie varbinary(8000) EXECUTE AS USER = 'nissenils' WITH COOKIE INTO @cookie REVERT WITH COOKIE = @cookie go 1000 SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type = 'USERSTORE_TOKENPERM' AND name = 'TokenAndPermUserStore' DROP USER nissenils go CREATE LOGIN nissenils WITH PASSWORD = 'NotThatSecret' CREATE USER nissenils go DECLARE @cookie varbinary(8000) EXECUTE AS LOGIN = 'nissenils' WITH COOKIE INTO @cookie REVERT WITH COOKIE = @cookie go 1000 SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type = 'USERSTORE_TOKENPERM' AND name = 'TokenAndPermUserStore' DROP USER nissenils DROP LOGIN nissenils go DBCC FREESYSTEMCACHE('TokenAndPermUserStore') SELECT pages_kb FROM sys.dm_os_memory_clerks WHERE type = 'USERSTORE_TOKENPERM' AND name = 'TokenAndPermUserStore'