How to Give the NT Service\MSSQLSERVER service account the permission of Lock Page in Memory in SQL Server 2012

·        Verify that the feature of the locked pages is not ON because the start-up account for SQL Server 2012 does not have the permission "lock pages in memory" by default.

--The table is still in the old database. You need to delete it.

--Option 1 - locked_page_allocations_kb should be 0
SELECT * FROM sys.dm_os_memory_nodes
 
--Option 2: check the memory manager pane and note the locked ages allocated is 0
DBCC memorystatus

·        As you may know, in SQL Server 2012, the default start-up service account is a virtual account. In my case, it is NT Service\MSSQLSERVER.

·        In SQL Server 2012, we can enable "locked pages" by granting the "lock pages in memory" user right for the startup account for the instance of SQL Server as below:

o   Run: gpedit.msc, 
o   Find the option: Computer Configuration|Windows Settings|Security Settings|Local Policies|User Right Assignments|Local Pages in Memory (the right panel), double click, Add User or Group.
o   Now is the trick part, you cannot find NT Service\MSSQLSERVER because it is not a user, a group, a built-in security principal.
o   Solution: You can either directly type NT Service\MSSQLSERVER or copy the service account from SQL Server Configuration Manager to box, then click OK, it will show several items. Select the first one: MSSQLServer. That's it! It is NT Service\MSSQLSERVER.
o   Restart SQL Server, verify the "locked page" feature is ON now.