This topic comes up from time to time in my daily support work, so I thought I would make a quick post on the rights required if you do not want the SQL Server service account to be a member of the Local Administrators group on Windows. Having the SQL Server service account as a member of the local Windows Administrator group is not recommended.
From version to version, the list of rights required for the SQL Server service account can vary. With our focus and commitment to security, the rights required in each subsequent version tend to be less - reducing your overall exposure. However, these changes and variations in required rights from version to version make it a bit challenging to cover in a single blog post. Below are the respective articles for each version that outline the permissions assigned to the service accounts:
Configure Windows Service Accounts and Permissions (SQL Server 2012)http://msdn.microsoft.com/en-us/library/ms143504.aspx#Windows
Setting Up Windows Service Accounts (SQL Server 2008 R2)http://msdn.microsoft.com/en-us/library/ms143504(v=sql.105).aspx#Review_NT_rights
Setting Up Windows Service Accounts (SQL Server 2008)http://msdn.microsoft.com/en-us/library/ms143504(v=sql.100).aspx#Review_NT_rights
Setting Up Windows Service Accounts (SQL Server 2005)http://msdn.microsoft.com/en-us/library/ms143504(v=sql.90).aspx#Review_NT_rights
Note: Lock Pages in Memory is only required for those 32 bit systems with AWE enabled. It is recommended, but not required, for 64 bit systems to prevent buffer pool memory from being paged out of physical memory: http://support.microsoft.com/kb/918483
One additional right that may interest you on any SQL Server 2005 and later instance is “Perform Volume Maintenance Tasks”. This was added in Windows 2003 and SQL Server 2005 and later can take advantage of this new feature to allow “Instant File Initialization”. This new feature allows a file allocation request – like a file growth in SQL Server – to skip zero initialization. Zero initialization is a security feature and required by many security audit standards, so you will want to consider this carefully and take any legal obligations and regulatory compliance you are under into account before enabling it. Since this new right skips the zeroing out of newly allocated space, database file growths are very quick.
However, this does not replace proper database file management. You should still size your database files to avoid file growths. Rely on the auto-growth feature as a safety catch for unexpected, emergency situations.