Configuring security rights for a SQL Server account

During the installation of the SQL Server, you're choosing an account that will be used to run all SQL Server services. There are quite a few considerations for a proper choice but, in this chapter, we'll focus more on the follow-up configuration. Such an account needs to have proper rights on the system. System rights can be configured via Group Policy Editor in the Computer Configuration segment of the policy. To open up the console for the rights configuration, perform the following steps:

1. Run gpedit.msc.
2. Expand Computer Configuration | Windows Settings | Security Settings | Local Policies.

1. Double-click on the system right that you want to edit.
2. Add the account or group to which you want to grant the rights.

There are several rights that we'll consider for the SQL Server. The first one will be Perform volume maintenance Tasks. This right can be granted directly during the installation of the SQL Server, but if you skip this, here's where and how you can add this right to your SQL Server account. The reason for granting this right is to enable Instant File Initialization, which can speed up disk operations to allocate new space for data files on the disk. Instant File Initialization does not work for log files, which in any case have to be zeroed out.

Instant File Initialization is used when the data file for the database is growing and allocating new space on the disk drive and also during the restoring of the database to create all files on the disk, before data can be copied from backup to the data files:

Another system right that we will assign as part of post-installation configuration will be Generate Security Audits. As you can see in the previous screenshot, this right is granted to two accounts, Local Service and Network Service. Our SQL Server is running with a different account and this account needs to be added to the list. This right will, later on, allow our SQL Server to store audit events in Windows Event Log to the security log portion. This may come handy once we see how SQL Server audit is working and what are the options to audit.

The last system right that we will assign is Lock Pages In Memory. This right will allow SQL Server to lock the memory pages and prevent Windows OS to page out memory in a case of memory pressure on the operating system. This one has to be taken with careful consideration and more configuration on the SQL Server engine and proper system monitoring. We'll talk about the SQL Server settings later.