SQL Server – Determine if Instance Has ‘Lock Pages in Memory’ Right

sql serversql-server-2008-r2sql-server-2012

The "Lock Pages in Memory" right can be granted to the service account used by SQL Server. This allows SQL Server to prevent memory being paged to disk.

I've noticed several of our SQL Server machines do not have the local policy configured to allow this right for the service account used by SQL Server. Since we have a large number of servers, it will be tedious at best to manually check each one using the "Local System Policy" management console.

Is there a T-SQL query, extended stored procedure, or some other method I can use to determine if the server in question has the right or not?

I would like to not use EXEC xp_readerrorlog 0, 1, 'lock memory privilege was not granted'; since it relies on checking the current SQL Server error log; and that error log may not contain the relevant entry assuming the log has been rolled over since the server was last restarted. I realize I could check older logs by modify the first parameter from 0 to 1, etc, however we only retain 10 error logs, and that may not be enough. I want a fail-safe way of confirming the setting.

Best Answer

If xp_cmdshell is an option, here is a script making use of whoami:

DECLARE @LockPagesInMemory VARCHAR(255);
SET @LockPagesInMemory = 'UNKNOWN';
DECLARE @Res TABLE
(
    [output] NVARCHAR(255) NULL
);

IF (SELECT value_in_use
    FROM sys.configurations c
    WHERE c.name = 'xp_cmdshell'
    ) = 1
BEGIN
    INSERT INTO @Res
    EXEC xp_cmdshell 'WHOAMI /PRIV';

    IF EXISTS (SELECT *
        FROM @Res 
        WHERE [output] LIKE 'SeLockMemoryPrivilege%'
        )
        SET @LockPagesInMemory = 'ENABLED';
    ELSE
        SET @LockPagesInMemory = 'DISABLED';
END

SELECT LockPagesInMemoryEnabled = @LockPagesInMemory;

whoami.exe has the ability to report all the rights held by the account executing whoami. This, combined with the xp_cmdshell provides a reliable way to determine if the instance of SQL Server has the necessary right to lock pages in memory.

If xp_cmdshell is not enabled, this code returns UNKNOWN. Assuming xp_cmdshell is enabled, and the right is enabled for the SQL Server account, it returns ENABLED, otherwise it returns DISABLED.

Related Question