Check the Lock Pages in Memory privilege
Check from T-SQL whether the SQL Server service account holds the SeLockMemoryPrivilege right, using xp_cmdshell.
Lock Pages in Memory (LPIM) lets the SQL Server process keep its buffer pool in physical memory. The Windows account that runs the SQL Server service needs the SeLockMemoryPrivilege right. The query below runs WHOAMI /PRIV through xp_cmdshell and reports ENABLED, DISABLED or UNKNOWN.
Enable xp_cmdshell
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
Check the privilege
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;
Disable xp_cmdshell after the check
EXEC sp_configure 'xp_cmdshell', 0;
RECONFIGURE;
Notes
UNKNOWNmeans thatxp_cmdshellis disabled, so the privilege was not checked.xp_cmdshellruns operating system commands from SQL Server. Enable it only for the time of the check.- Without a proxy account,
xp_cmdshellruns under the SQL Server service account, so the result shows the rights of that account.