Check SQL Server missing update KB2277078 to prevent leak of security audit entries in TokenAndPermUserStore
Introduction
This script will demo how to check SQL Server missing update KB2277078 to prevent leak of security audit entries in TokenAndPermUserStore.
Scenarios
When we create and enable a SQL Server audit on a computer that is running Microsoft SQL Server 2008 R2 or Microsoft SQL Server 2008, the TokenAndPermUserStore cache store continues to grow in size. Additionally, the size does not decrease even when we
run the DBCC FREESYSTEMCACHE command, this issue occurs because a memory leak occurs when a SQL Server audit is enabled on an object. To fix it, for different versions, we should install the corresponding updates.
Script
This script is used to determine whether this instance of SQL Server contains any defined audits and whether the fix is applied. If the current build version of SQL Server is earlier than the fix build version, the advisor generates an alert about this
issue. Customer can use this script by following below steps:
Step1: Launch the Windows PowerShell. Click Start, point to
All Programs, point to Accessories, point to
Windows PowerShell, right-click Windows PowerShell, and select
Run As Administrator.
Screenshot:
Screenshot:
Step2: Locate to the script path and load the script into current session.
Command: . E:\Onescript\CheckSQLInstanceVersionNumber.ps1
Screenshot:
Note: Replace the E:\Onescript\ with your path.
Command: . E:\Onescript\CheckSQLInstanceVersionNumber.ps1
Screenshot:
Note: Replace the E:\Onescript\ with your path.
Step3: Call the advanced function Check-OSCUpdate, the screen will prompt you enter in the server\instance names.
Command: Check-OSCUpdate
Screenshot:
Command: Check-OSCUpdate
Screenshot:
Step4: To enter the server\instance names, split wih ‘,’.
Command: server1\instance1,server2\instance1
Screenshot:
Command: server1\instance1,server2\instance1
Screenshot:
Step5: Choose the authentication type.
Command: Y
Screenshot:
Note: “Y” for Windows Integrated authentication mode, “N” for the SQL Server authentication mode.
Command: Y
Screenshot:
Note: “Y” for Windows Integrated authentication mode, “N” for the SQL Server authentication mode.
Step6: If entered “Y” in previous step,
please enter the user name and password for a domain account.
Screenshot:
Screenshot:
If entered “N” in previous step, please enter the login name and password for a SQL Server login account.
Screenshot:
Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.
PowerShell
Edit|Remove
powershell
Function Check-OSCUpdate()
{
$InputInstances = read-host -prompt "Please enter the server\instance names, split them with ',', such as: server1,server2\instance1"
$ArrayInstances = $InputInstances.split(",")
$AuthenticationType = read-host -prompt "Using Windows Integrated Authentication, Y/N?"
If ($AuthenticationType -eq "N")
{
$User= read-host -prompt "Please enter the Login name"
$PassWord=read-host -assecurestring "Please enter the password"
}
elseif ($AuthenticationType -eq "Y")
{
$Credential=get-credential
$User= $Credential.UserName
$PassWord = $Credential.Password
}
else
{
break
}
Function Check-OSCUpdate() { $InputInstances = read-host -prompt "Please enter the server\instance names, split them with ',', such as: server1,server2\instance1" $ArrayInstances = $InputInstances.split(",") $AuthenticationType = read-host -prompt "Using Windows Integrated Authentication, Y/N?" If ($AuthenticationType -eq "N") { $User= read-host -prompt "Please enter the Login name" $PassWord=read-host -assecurestring "Please enter the password" } elseif ($AuthenticationType -eq "Y") { $Credential=get-credential $User= $Credential.UserName $PassWord = $Credential.Password } else { break }