Category Archives: SQL Scripts

How to check Lock Pages In Memory is enabled?

You can use below simple technique to check whether lock pages in memory is enabled or not. If lock pages in memory is enabled you can see the “Using locked pages for buffer pool” message in the SQL Server error log.

exec xp_readerrorlog 0, 1, 'locked pages'

To check if it is disabled. You have to check for the “Address Windowing Extensions (AWE) requires the ‘lock pages in memory’ privilege which is not currently present in the access token of the process.” message.

exec xp_readerrorlog 0, 1, 'lock pages in memory'

T-SQL script to Identify the SQL Server version and edition

Different ways to make a table read only in a SQL Server database

Problem

In some cases there may be a need to make a SQL Server table read only. There are several different options for doing this and in this tip we cover various ways that you can make a table read only in a SQL Server database.

Solution

http://www.mssqltips.com/sqlservertip/2711/different-ways-to-make-a-table-read-only-in-a-sql-server-database/#comments

SQL Server Login Failure Error 18456, Severity 14, State 10

1 Reply

Problem
Recently I experienced a situation where a SQL Server login failed with this error message:
Error: 18456, Severity: 14, State: 10.
Login failed for user ‘SQL_Login’. Reason: Password validation failed with an infrastructure error. Check for previous errors. [CLIENT: XXX.XXX.XXX.XXX].

Can you help me decipher this SQL Server issue and correct it? Check out this tip to learn more.

Solution
http://www.mssqltips.com/sqlservertip/2679/sql-server-login-failure-error-18456-severity-14-state-10/

Script to drop all SQL Logins

1 Reply

Recently I got a requirement to drop all SQL Logins from the SQL Server. I have created below script to drop all SQL Login.

Before getting into detail, please take a note that “A login cannot be dropped while it is logged in. A login that owns any securable, server-level object, or SQL Server Agent job cannot be dropped”

SA login account and the few policy certificates also come in SQL Login type, I have excluded them.

select *
from sys.server_principals
where name = 'sa' or substring(name, 1, 1) = '#'

select 'DROP LOGIN ' + [name]
from sys.server_principals
where type_desc = 'SQL_LOGIN'
and sid <> 0x01 and substring(name, 1, 1) <> '#'

Next step is to execute the output of the above query.

aiDeeva.com

AI for Thinkers, Builders, and Believers.