Follow-on from instant initialization privilege checking

After my previous post on checking whether a SQL instance is able to use instant initialization (see here), I had a discussion with Scott R., who regularly comments on blog articles. He proposed an alternative method of checking whether the SQL service account has the Perform volume maintenance tasks privilege (AKA SeManageVolumePrivilege) (or other useful privileges like Lock pages in memory – SeLockMemoryPrivilege) and outlined the pros and cons of the various methods. With his permission, I’ve turned the information he provided into this blog post.

To use whoami /priv to find the SQL service account privileges you need to enable xp_cmdshell to do it from within SQL, or be logged into the Windows box as the SQL service account. Scott suggested using an alternative tool called AccessChk, written by my friend Mark Russinovich (formerly of Sysinternals, now a Technical Fellow at Microsoft). Using it you can find the privileges assigned to other users, services, or processes. The example syntax to do this for a SQL instance would be:

AccessChk –p sqlservr.exe –f –q -v

This allows you to find the privileges of the SQL service account without having to enable xp_cmdshell or login as the service account itself.

Let’s compare the whoami + xp_cmdshell combination with the AccessChk + command window combination.

Whoami + xp_cmdshell

Advantages:

• You don’t need access to a command window.
  
• You don’t need an administrator-capable user account.
  
• Whoami may already be installed on a given Windosw server.

Disadvantages:

• You need to enable xp_cmdshell (for a brief period – and have the authority to do so) to run whoami, which is often a separate security issue in many IT organizations.
  
• Whoami can’t be run from a separate command window, because it can’t get the privileges from any user account other than the one which is running the command.  Since the goal is to get the privileges of the SQL service account, and that service account is often locked down from unnecessary privileges (such as interactive logon, using command windows, etc.), whoami can’t easily be used to derive this information outside the context of running from SQL Server via xp_cmdshell.
  
• Whoami may not already be installed on a given Windows server.  This could be a barrier to using it for this purpose in IT organizations with rigid program install guidelines.

AccessChk + command window

Advantages:

• You don’t need to enable xp_cmdshell, bypassing this separate security issue.
  
• You don’t need to use the SQL service account as AccessChk can report on privileges of other running processes (and their implied user account) or other explicit user accounts, without requiring the security context of the desired user account to run AccessChk.
  
• This approach may be used to automate capture of such information for configuration management and reporting purposes.

Disadvantages:

• You need access to a command window on the Windows server.
  
• You may need an administrator-capable user account (which you may not have) – I am not sure of this requirement, but it may be the case.
  
• AccessChk is most likely not already installed on a given Windows server.  This could be a barrier to using it for this purpose in IT organizations with rigid program install guidelines, just like for whoami.

Summary

So is there a recommendation here? No, just a quick examination of the trade-offs with the two methods. Once again, I recommend you grant this privilege to the SQL service account for the massive performance boost it gives with data file creation or growth operations.