Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Scripts to check SQL Server instant file initialization and lock pages in memory for all servers


By:   |   Read Comments   |   Related Tips: More > Database Configurations

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem

As you may know, there are some performance boosters for SQL Server, and you need to check if you have them set up, or determine if you need to enable them on some of your servers. So after reading this article, I wondered if there was a way to check SQL Server versions prior to 2016? Certainly there is a way, but building the tool requires you to know: PowerShell, C#, system DLL’s, pointers, Active Directory, and parallel execution. Is there an easier option?

Solution

As a lot of articles point out, there isn’t a direct way to query the system, but I found this article that uses, through PowerShell and C# classes, a system library named “advapi32.dll”, described as an “Advanced services library supporting numerous APIs including many security and registry calls”. The main advantage is that it allows you to query remote servers.

So the first step was to separate the C# class in the article, in order to benefit from a clear separation of functions and have code highlighting and intellisense, into the “advapi.cs” code below but with a slight change; I don’t have .NET 4, so the function “IntPtr.Add” doesn’t exist. The solution was, with the help of this article, to replace it with what it does have, perform a basic pointer addition and creating a new pointer.

The second step was to find the commands to get the users contained in a remote machine local group, which is:

$acct = [adsi]"WinNT://$serverName/$acct,group" 
$accts = $acct.psbase.Invoke("Members") | select @{Name="Name"; Expression={$_.GetType().InvokeMember("AdsPath", "GetProperty", $null, $_, $null).replace("WinNT://", "").replace("/","\")}} 

And the command to get the users contained in a domain group, which is:

$result = net group $acct /domain   

This is required because sometimes the SQL Server account isn’t granted the permissions directly, but instead, through a local group (like “Administrators”) or through a domain group (like “Database administrators”), or even more often, the local Windows “Administrators” group that contains the domain group “Database administrators” which contains the SQL Server account.

The result is the below script named “GetInstantFileInitLockPagesMemory.ps1”, which works in the following way:

  1. Query a local inventory of instances, in which I have the server name and the environment (Production, Test, or Development). I get only unique server names, because there are several instances within the same server and I don’t want to query them more than once.
  2. Define the path where I have located the file “advapi.cs”, because multiple threads are going to be launched as separate processes and these don’t know where they’re being launched from.
  3. Define the domain name and the SQL Server account. Note that if you don’t have a domain account, this script won’t work for you, and you may need to tune it for your needs.
  4. Define the permissions we want to check, for our purposes we will check for “SeManageVolumePrivilege” and “SeLockMemoryPrivilege”. You can check others as defined in the file “advapi.cs” in the enumeration “Rights”.
  5. Launch several parallel threads, for each of the servers:
    1. Get the accounts with a user permission, i.e. manage volume privileges.
    2. Check if the SQL Server account is contained in this list. If not, proceed with next step. If no accounts are granted, continue with the next permission.
    3. For all local groups:
      1. Get the members and check if the account is contained in this list. If not, proceed to the next step.
      2. Get the members of the domain groups assigned to the remote machine local group and check if the account is contained in this list.
      3. Note that, if the nesting of groups has more levels, i.e. there is another group within the “Database administrators” and the SQL Server account is assigned to it, this script won’t be able to find it.
    4. For all domain groups:
      1. Get the members and check if the account is contained in this list.
      2. Note that, if the nesting of groups has more levels, this script won’t be able to find it.
    5. Start again doing the same for the next permission, i.e. lock pages in memory.
  6. If there is a missing permission, this script will return the environment, the server name, and all missing permission. If all permissions are granted, nothing is reported. If there is an error, it shows the environment, server name, last exit code and all error messages.

Download the source code here.

Code for advapi.cs

using System;
namespace PS_LSA
{
    using System.ComponentModel;
    using System.Runtime.InteropServices;
    using System.Security;
    using System.Security.Principal;
    using LSA_HANDLE = IntPtr;

    public enum Rights
    {
        SeTrustedCredManAccessPrivilege,      // Access Credential Manager as a trusted caller
        SeNetworkLogonRight,                  // Access this computer from the network
        SeTcbPrivilege,                       // Act as part of the operating system
        SeMachineAccountPrivilege,            // Add workstations to domain
        SeIncreaseQuotaPrivilege,             // Adjust memory quotas for a process
        SeInteractiveLogonRight,              // Allow log on locally
        SeRemoteInteractiveLogonRight,        // Allow log on through Remote Desktop Services
        SeBackupPrivilege,                    // Back up files and directories
        SeChangeNotifyPrivilege,              // Bypass traverse checking
        SeSystemtimePrivilege,                // Change the system time
        SeTimeZonePrivilege,                  // Change the time zone
        SeCreatePagefilePrivilege,            // Create a pagefile
        SeCreateTokenPrivilege,               // Create a token object
        SeCreateGlobalPrivilege,              // Create global objects
        SeCreatePermanentPrivilege,           // Create permanent shared objects
        SeCreateSymbolicLinkPrivilege,        // Create symbolic links
        SeDebugPrivilege,                     // Debug programs
        SeDenyNetworkLogonRight,              // Deny access this computer from the network
        SeDenyBatchLogonRight,                // Deny log on as a batch job
        SeDenyServiceLogonRight,              // Deny log on as a service
        SeDenyInteractiveLogonRight,          // Deny log on locally
        SeDenyRemoteInteractiveLogonRight,    // Deny log on through Remote Desktop Services
        SeEnableDelegationPrivilege,          // Enable computer and user accounts to be trusted for delegation
        SeRemoteShutdownPrivilege,            // Force shutdown from a remote system
        SeAuditPrivilege,                     // Generate security audits
        SeImpersonatePrivilege,               // Impersonate a client after authentication
        SeIncreaseWorkingSetPrivilege,        // Increase a process working set
        SeIncreaseBasePriorityPrivilege,      // Increase scheduling priority
        SeLoadDriverPrivilege,                // Load and unload device drivers
        SeLockMemoryPrivilege,                // Lock pages in memory
        SeBatchLogonRight,                    // Log on as a batch job
        SeServiceLogonRight,                  // Log on as a service
        SeSecurityPrivilege,                  // Manage auditing and security log
        SeRelabelPrivilege,                   // Modify an object label
        SeSystemEnvironmentPrivilege,         // Modify firmware environment values
        SeManageVolumePrivilege,              // Perform volume maintenance tasks
        SeProfileSingleProcessPrivilege,      // Profile single process
        SeSystemProfilePrivilege,             // Profile system performance
        SeUnsolicitedInputPrivilege,          // "Read unsolicited input from a terminal device"
        SeUndockPrivilege,                    // Remove computer from docking station
        SeAssignPrimaryTokenPrivilege,        // Replace a process level token
        SeRestorePrivilege,                   // Restore files and directories
        SeShutdownPrivilege,                  // Shut down the system
        SeSyncAgentPrivilege,                 // Synchronize directory service data
        SeTakeOwnershipPrivilege              // Take ownership of files or other objects
    }

    [StructLayout(LayoutKind.Sequential)]
    struct LSA_OBJECT_ATTRIBUTES
    {
        internal int Length;
        internal IntPtr RootDirectory;
        internal IntPtr ObjectName;
        internal int Attributes;
        internal IntPtr SecurityDescriptor;
        internal IntPtr SecurityQualityOfService;
    }

    [StructLayout(LayoutKind.Sequential, CharSet = CharSet.Unicode)]
    struct LSA_UNICODE_STRING
    {
        internal ushort Length;
        internal ushort MaximumLength;
        [MarshalAs(UnmanagedType.LPWStr)]
        internal string Buffer;
    }

    [StructLayout(LayoutKind.Sequential)]
    struct LSA_ENUMERATION_INFORMATION
    {
        internal IntPtr PSid;
    }

    internal sealed class Win32Sec
    {
        [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
        internal static extern uint LsaOpenPolicy(
            LSA_UNICODE_STRING[] SystemName,
            ref LSA_OBJECT_ATTRIBUTES ObjectAttributes,
            int AccessMask,
            out IntPtr PolicyHandle
        );

        [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
        internal static extern uint LsaAddAccountRights(
            LSA_HANDLE PolicyHandle,
            IntPtr pSID,
            LSA_UNICODE_STRING[] UserRights,
            int CountOfRights
        );

        [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
        internal static extern uint LsaRemoveAccountRights(
            LSA_HANDLE PolicyHandle,
            IntPtr pSID,
            bool AllRights,
            LSA_UNICODE_STRING[] UserRights,
            int CountOfRights
        );

        [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
        internal static extern uint LsaEnumerateAccountRights(
            LSA_HANDLE PolicyHandle,
            IntPtr pSID,
            out IntPtr /*LSA_UNICODE_STRING[]*/ UserRights,
            out ulong CountOfRights
        );

        [DllImport("advapi32", CharSet = CharSet.Unicode, SetLastError = true)]
        internal static extern uint LsaEnumerateAccountsWithUserRight(
            LSA_HANDLE PolicyHandle,
            LSA_UNICODE_STRING[] UserRights,
            out IntPtr EnumerationBuffer,
            out ulong CountReturned
        );

        [DllImport("advapi32")]
        internal static extern int LsaNtStatusToWinError(int NTSTATUS);

        [DllImport("advapi32")]
        internal static extern int LsaClose(IntPtr PolicyHandle);

        [DllImport("advapi32")]
        internal static extern int LsaFreeMemory(IntPtr Buffer);
    }

    internal sealed class Sid : IDisposable
    {
        public IntPtr pSid = IntPtr.Zero;
        public SecurityIdentifier sid = null;

        public Sid(string account)
        {
            try { sid = new SecurityIdentifier(account); }
            catch { sid = (SecurityIdentifier)(new NTAccount(account)).Translate(typeof(SecurityIdentifier)); }
            Byte[] buffer = new Byte[sid.BinaryLength];
            sid.GetBinaryForm(buffer, 0);

            pSid = Marshal.AllocHGlobal(sid.BinaryLength);
            Marshal.Copy(buffer, 0, pSid, sid.BinaryLength);
        }

        public void Dispose()
        {
            if (pSid != IntPtr.Zero)
            {
                Marshal.FreeHGlobal(pSid);
                pSid = IntPtr.Zero;
            }
            GC.SuppressFinalize(this);
        }
        ~Sid() { Dispose(); }
    }

    public sealed class LsaWrapper : IDisposable
    {
        enum Access : int
        {
            POLICY_READ = 0x20006,
            POLICY_ALL_ACCESS = 0x00F0FFF,
            POLICY_EXECUTE = 0X20801,
            POLICY_WRITE = 0X207F8
        }
        const uint STATUS_ACCESS_DENIED = 0xc0000022;
        const uint STATUS_INSUFFICIENT_RESOURCES = 0xc000009a;
        const uint STATUS_NO_MEMORY = 0xc0000017;
        const uint STATUS_OBJECT_NAME_NOT_FOUND = 0xc0000034;
        const uint STATUS_NO_MORE_ENTRIES = 0x8000001a;

        IntPtr lsaHandle;

        public LsaWrapper() : this(null) { } // local system if systemName is null
        public LsaWrapper(string systemName)
        {
            LSA_OBJECT_ATTRIBUTES lsaAttr;
            lsaAttr.RootDirectory = IntPtr.Zero;
            lsaAttr.ObjectName = IntPtr.Zero;
            lsaAttr.Attributes = 0;
            lsaAttr.SecurityDescriptor = IntPtr.Zero;
            lsaAttr.SecurityQualityOfService = IntPtr.Zero;
            lsaAttr.Length = Marshal.SizeOf(typeof(LSA_OBJECT_ATTRIBUTES));
            lsaHandle = IntPtr.Zero;
            LSA_UNICODE_STRING[] system = null;
            if (systemName != null)
            {
                system = new LSA_UNICODE_STRING[1];
                system[0] = InitLsaString(systemName);
            }

            uint ret = Win32Sec.LsaOpenPolicy(system, ref lsaAttr, (int)Access.POLICY_ALL_ACCESS, out lsaHandle);
            if (ret == 0) return;
            if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
            if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
            throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
        }

        public void AddPrivilege(string account, Rights privilege)
        {
            uint ret = 0;
            using (Sid sid = new Sid(account))
            {
                LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1];
                privileges[0] = InitLsaString(privilege.ToString());
                ret = Win32Sec.LsaAddAccountRights(lsaHandle, sid.pSid, privileges, 1);
            }
            if (ret == 0) return;
            if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
            if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
            throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
        }

        public void RemovePrivilege(string account, Rights privilege)
        {
            uint ret = 0;
            using (Sid sid = new Sid(account))
            {
                LSA_UNICODE_STRING[] privileges = new LSA_UNICODE_STRING[1];
                privileges[0] = InitLsaString(privilege.ToString());
                ret = Win32Sec.LsaRemoveAccountRights(lsaHandle, sid.pSid, false, privileges, 1);
            }
            if (ret == 0) return;
            if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
            if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
            throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
        }

        public Rights[] EnumerateAccountPrivileges(string account)
        {
            uint ret = 0;
            ulong count = 0;
            IntPtr privileges = IntPtr.Zero;
            Rights[] rights = null;

            using (Sid sid = new Sid(account))
            {
                ret = Win32Sec.LsaEnumerateAccountRights(lsaHandle, sid.pSid, out privileges, out count);
            }
            if (ret == 0)
            {
                rights = new Rights[count];
                for (int i = 0; i < (int)count; i++)
                {
                    LSA_UNICODE_STRING str = (LSA_UNICODE_STRING)Marshal.PtrToStructure(
                        //IntPtr.Add(privileges, i * Marshal.SizeOf(typeof(LSA_UNICODE_STRING))),
      new IntPtr(privileges.ToInt64() + (i * Marshal.SizeOf(typeof(LSA_UNICODE_STRING)))),
                        typeof(LSA_UNICODE_STRING));
                    rights[i] = (Rights)Enum.Parse(typeof(Rights), str.Buffer);
                }
                Win32Sec.LsaFreeMemory(privileges);
                return rights;
            }
            if (ret == STATUS_OBJECT_NAME_NOT_FOUND) return null;  // No privileges assigned
            if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
            if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
            throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
        }

        public string[] EnumerateAccountsWithUserRight(Rights privilege)
        {
            uint ret = 0;
            ulong count = 0;
            LSA_UNICODE_STRING[] rights = new LSA_UNICODE_STRING[1];
            rights[0] = InitLsaString(privilege.ToString());
            IntPtr buffer = IntPtr.Zero;
            string[] accounts = null;

            ret = Win32Sec.LsaEnumerateAccountsWithUserRight(lsaHandle, rights, out buffer, out count);
            if (ret == 0)
            {
                accounts = new string[count];
                for (int i = 0; i < (int)count; i++)
                {
                    LSA_ENUMERATION_INFORMATION LsaInfo = (LSA_ENUMERATION_INFORMATION)Marshal.PtrToStructure(
                        //IntPtr.Add(buffer, i * Marshal.SizeOf(typeof(LSA_ENUMERATION_INFORMATION))),
      new IntPtr(buffer.ToInt64() + (i * Marshal.SizeOf(typeof(LSA_ENUMERATION_INFORMATION)))),
                        typeof(LSA_ENUMERATION_INFORMATION));

                    try {
                        accounts[i] = (new SecurityIdentifier(LsaInfo.PSid)).Translate(typeof(NTAccount)).ToString();
                    } catch (System.Security.Principal.IdentityNotMappedException) {
                        accounts[i] = (new SecurityIdentifier(LsaInfo.PSid)).ToString();
                    }
                }
                Win32Sec.LsaFreeMemory(buffer);
                return accounts;
            }
            if (ret == STATUS_NO_MORE_ENTRIES) return null;  // No accounts assigned
            if (ret == STATUS_ACCESS_DENIED) throw new UnauthorizedAccessException();
            if ((ret == STATUS_INSUFFICIENT_RESOURCES) || (ret == STATUS_NO_MEMORY)) throw new OutOfMemoryException();
            throw new Win32Exception(Win32Sec.LsaNtStatusToWinError((int)ret));
        }

        public void Dispose()
        {
            if (lsaHandle != IntPtr.Zero)
            {
                Win32Sec.LsaClose(lsaHandle);
                lsaHandle = IntPtr.Zero;
            }
            GC.SuppressFinalize(this);
        }
        ~LsaWrapper() { Dispose(); }

        // helper functions:
        static LSA_UNICODE_STRING InitLsaString(string s)
        {
            // Unicode strings max. 32KB
            if (s.Length > 0x7ffe) throw new ArgumentException("String too long");
            LSA_UNICODE_STRING lus = new LSA_UNICODE_STRING();
            lus.Buffer = s;
            lus.Length = (ushort)(s.Length * sizeof(char));
            lus.MaximumLength = (ushort)(lus.Length + sizeof(char));
            return lus;
        }
    }

    public sealed class TokenManipulator
    {
        [StructLayout(LayoutKind.Sequential, Pack = 1)]
        internal struct TokPriv1Luid
        {
            public int Count;
            public long Luid;
            public int Attr;
        }

        internal const int SE_PRIVILEGE_DISABLED = 0x00000000;
        internal const int SE_PRIVILEGE_ENABLED = 0x00000002;
        internal const int TOKEN_QUERY = 0x00000008;
        internal const int TOKEN_ADJUST_PRIVILEGES = 0x00000020;

        internal sealed class Win32Token
        {
            [DllImport("advapi32.dll", ExactSpelling = true, SetLastError = true)]
            internal static extern bool AdjustTokenPrivileges(
                IntPtr htok,
                bool disall,
                ref TokPriv1Luid newst,
                int len,
                IntPtr prev,
                IntPtr relen
            );

            [DllImport("kernel32.dll", ExactSpelling = true)]
            internal static extern IntPtr GetCurrentProcess();

            [DllImport("advapi32.dll", ExactSpelling = true, SetLastError = true)]
            internal static extern bool OpenProcessToken(
                IntPtr h,
                int acc,
                ref IntPtr phtok
            );

            [DllImport("advapi32.dll", SetLastError = true)]
            internal static extern bool LookupPrivilegeValue(
                string host,
                string name,
                ref long pluid
            );

            [DllImport("kernel32.dll", ExactSpelling = true)]
            internal static extern bool CloseHandle(
                IntPtr phtok
            );
        }

        public static void AddPrivilege(Rights privilege)
        {
            bool retVal;
            int lasterror;
            TokPriv1Luid tp;
            IntPtr hproc = Win32Token.GetCurrentProcess();
            IntPtr htok = IntPtr.Zero;
            retVal = Win32Token.OpenProcessToken(hproc, TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY, ref htok);
            tp.Count = 1;
            tp.Luid = 0;
            tp.Attr = SE_PRIVILEGE_ENABLED;
            retVal = Win32Token.LookupPrivilegeValue(null, privilege.ToString(), ref tp.Luid);
            retVal = Win32Token.AdjustTokenPrivileges(htok, false, ref tp, Marshal.SizeOf(tp), IntPtr.Zero, IntPtr.Zero);
            Win32Token.CloseHandle(htok);
            lasterror = Marshal.GetLastWin32Error();
            if (lasterror != 0) throw new Win32Exception();
        }

        public static void RemovePrivilege(Rights privilege)
        {
            bool retVal;
            int lasterror;
            TokPriv1Luid tp;
            IntPtr hproc = Win32Token.GetCurrentProcess();
            IntPtr htok = IntPtr.Zero;
            retVal = Win32Token.OpenProcessToken(hproc, TOKEN_ADJUST_PRIVILEGES | TOKEN_QUERY, ref htok);
            tp.Count = 1;
            tp.Luid = 0;
            tp.Attr = SE_PRIVILEGE_DISABLED;
            retVal = Win32Token.LookupPrivilegeValue(null, privilege.ToString(), ref tp.Luid);
            retVal = Win32Token.AdjustTokenPrivileges(htok, false, ref tp, Marshal.SizeOf(tp), IntPtr.Zero, IntPtr.Zero);
            Win32Token.CloseHandle(htok);
            lasterror = Marshal.GetLastWin32Error();
            if (lasterror != 0) throw new Win32Exception();
        }
    }
}

Code for GetInstantFileInitLockPagesMemory.ps1

$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "."
$database = "master"
$query = @"
SELECT DISTINCT [Environment], [ServerName]
  FROM [Inventory]
"@
$classPath = ".\advapi.cs"
$domain = "Domain\"
$account = "ServiceAccount"
$rights = "SeManageVolumePrivilege, SeLockMemoryPrivilege" -Split ", "
$SleepTimer = 1000 #after X milliseconds, check if the jobs have finished. 1000 is every second.
$MaxResultTime = 300 #after X seconds, all jobs are killed. 300 is 5 minutes.
$Maxthreads = 20 #number of parallel jobs
# import modules
Import-Module SqlPs -DisableNameChecking
$error.clear() #clear error generated by last command
# get list of servers
$objects = @(Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query)
#environment setup
$output = @()
$errors = ""
$Jobs = @()
$ISS = [system.management.automation.runspaces.initialsessionstate]::CreateDefault()
$RunspacePool = [runspacefactory]::CreateRunspacePool(1, $MaxThreads, $ISS, $Host)
$RunspacePool.Open()
$script = {
 Param ([string]$environment, [string]$serverName, [string]$classPath, [string]$domain, [string]$account, [string[]]$rights)
 $ErrorActionPreference = "Stop"
 try {
  try {
   Add-Type (Get-Content $classPath | Out-String)
  } catch { }
  $lsa = New-Object PS_LSA.LsaWrapper($serverName)
  $missingRights = @()
  foreach ($right in $rights) {
   $accounts = $lsa.EnumerateAccountsWithUserRight($right)
   if (!$accounts) { # no account has this permission assigned
    $missingRights += $right
    continue
   }
   if ($accounts -contains $domain+$account) { continue } # permission assigned explicitly to this account
   $found = $FALSE
   foreach ($val in ($accounts | where {$_.startsWith("BUILTIN\")})) { # only local groups
    $acct = $val.replace("BUILTIN\", "")
    $acct = [adsi]"WinNT://$serverName/$acct,group"
    $accts = $acct.psbase.Invoke("Members") | select @{Name="Name"; Expression={$_.GetType().InvokeMember("AdsPath", "GetProperty", $null, $_, $null).replace("WinNT://", "").replace("/","\")}}
    if ($accts -like "*"+$domain+$account+"*") { # user belongs to a group that has this permission assigned
     $found = $TRUE
     break
    }
    foreach ($val2 in ($accts | where {$_.Name.startsWith($domain) -and !$_.Name.contains($serverName)})) { # only domain groups
     $acct = $val2.Name.replace($domain, "")
     $result = $null
     try {
      $result = net group $acct /domain 2>&1 | where {$_.contains($account)}
     } catch { }
     if ($result) {
      $found = $TRUE
      break
     }
    }
    if ($found) {
     break
    }
   }
   foreach ($val in ($accounts | where {$_.startsWith($domain)})) { # only domain groups
    $acct = $val.replace($domain, "")
    $result = $null
    try {
     $result = net group $acct /domain 2>&1 | where {$_.contains($account)}
    } catch { }
    if ($result) {
     $found = $TRUE
     break
    }
   }
   if (!$found) { # user not assigned to a group that has this permission assigned
    $missingRights += $right
   }
  }
  "" #indicate there was no error
  if ($missingRights.count -gt 0) {
   @{'Environment'=$environment; 'ServerName'=$serverName; 'MissingRights'=$missingRights;}
  }
 }
 catch {
  return $environment + " " + $serverName + ": " + $LastExitCode + " " + $_ | foreach { $_.ToString() } | Out-String
 }
}
function CreateThread() {
 param ([string]$environment, [string]$serverName, [ref]$Jobs)
 $PowershellThread = [powershell]::Create().AddScript($script) #scriptToRun
 $PowershellThread.AddArgument($environment) | out-null
 $PowershellThread.AddArgument($serverName) | out-null
 $PowershellThread.AddArgument($classPath) | out-null
 $PowershellThread.AddArgument($domain) | out-null
 $PowershellThread.AddArgument($account) | out-null
 $PowershellThread.AddArgument($rights) | out-null
 $PowershellThread.RunspacePool = $RunspacePool
 $Handle = $PowershellThread.BeginInvoke()
 $Job = "" | select Handle, Thread, object
 $Job.Handle = $Handle; $Job.Thread = $PowershellThread
 $Jobs.value += $Job
}
$ResultTimer = Get-Date #start time
#start processing first task for each server
for ($i=0; $i -lt $objects.length; $i++) {
 CreateThread $objects[$i]."Environment" $objects[$i]."ServerName" ([ref]$Jobs)
}
while (@($Jobs | where {$_.Handle -ne $Null}).count -gt 0) {
 #update completed jobs, get errors and result, and dispose them
 foreach ($Job in @($Jobs | where {$_.Handle -ne $Null -and $_.Handle.IsCompleted -eq $True})) {
  $results = $Job.Thread.EndInvoke($Job.Handle)
  if ($results[0] -and $results[0] -ne "") {
   $errors += $results[0]
  }
  if ($results[1]) {
   $output += New-Object -Typename PSObject -Prop $results[1]
  }
  #end thread
  $Job.Thread.Dispose()
  $Job.Thread = $Null
  $Job.Handle = $Null
 }
 #show progress
 $inProgress = @($Jobs | where {$_.Handle.IsCompleted -eq $False}).count
 Write-Progress `
  -Activity "Gathering data" `
  -PercentComplete (($objects.length - $inProgress) * 100 / $objects.length) `
  -Status "$inProgress pending"
 #exit on timeout
 $currentTime = Get-Date
 if (($currentTime - $ResultTimer).totalseconds -gt $MaxResultTime) {
  Write-Error "Child script appears to be frozen, try increasing MaxResultTime"
  break
 }
 #sleep
 Start-Sleep -Milliseconds $SleepTimer
}
#dispose thread pools
$RunspacePool.Close() | Out-Null
$RunspacePool.Dispose() | Out-Null
$errors #return errors
$output | Format-Table -Auto #return results

Code Execution

The result is going to complete in less than 30 seconds for 153 servers, and while it is running, it shows the progress complete and the number of servers remaining:

Program running - Description: Program running

And at the end, it shows the results which you need to check:

Results - Description: Results
Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. Last year I switched jobs to a DBA position, where I've been suited to implement new processes and optimize existing ones.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools