Microsoft SQL Server Security check: Back-end queries to use

SQL Server, owned by Microsoft is one of the leading data platforms used as a production database to store very sensitive data. We should all know by now that data is an organization’s most valuable asset. This makes it a necessity to efficiently secure the SQL Server database.
This quick post will highlight some important back-end script that can be used to quickly check the status of your SQL Server database.
A comprehensive checklist is been developed and will be posted soon.
Enjoy......

Minimum Requirements:

1. Access to an SQL Server database
2. DB Roles required: db_reader

To check if guest account is still active
SELECT name, hasdbaccess
FROM sys.sysusers
WHERE name = 'guest'
Note:
hasdbaccess should be '0'
Guest account should NEVER be active in a production database


To check if the builtin administrator account has been removed.
SELECT r.name  as SrvRole, u.name  as LoginName  
FROM sys.server_role_members m JOIN
  sys.server_principals r ON m.role_principal_id = r.principal_id  JOIN
  sys.server_principals u ON m.member_principal_id = u.principal_id 
WHERE u.name = 'BUILTIN\Administrators'
Note:
Built in Administrator account should be disabled when the database is moved to production

To check whether the password policy is turn on or off
SELECT name  FROM sys.sql_logins 
 WHERE  is_policy_checked=0 OR is_expiration_checked = 0
Note:
A serious organisation should have a standards password policy in place.


To Check that Production and Sample databases are segregated
SELECT name FROM master.sys.databases 
 WHERE lower(name) LIKE '%test%' OR lower(name) LIKE '%dev%'
 OR lower(name) LIKE '%adventure%'
 OR lower(name) LIKE '%uat%'
 OR lower(name) LIKE 'pub%'
 OR lower(name) LIKE '%northwind%'
Note:
Sample databases like Pub, AdventureWorks and Northwind should not exist in a production database.


To check whether the "sa" password exists and if it does, Identify if the password policy is turned on for the "sa" login
SELECT p.name, CASE WHEN p.name = 'sa' THEN 'NO' ELSE 'YES' END as Renamed,
  s.is_policy_checked, s.is_expiration_checked, p.is_disabled
FROM sys.server_principals AS p
 LEFT OUTER JOIN sys.sql_logins AS s ON s.principal_id = p.principal_id
WHERE p.sid = 0x01


This will check different server configuration settings
SELECT name, value_in_use FROM sys.configurations
 WHERE configuration_id IN (16391, 102, 400, 1562, 16386, 16385, 16390, 16393)
Note:
Configuration_id 16393 is to check if "Contained Databases Authentication" option is enabled on SQL Server 2012.
There are known security threats associated with contained databases.


To check for SQL Server Authentication mode
SELECT SERVERPROPERTY ('IsIntegratedSecurityOnly')
Note:
- If this returns 0 the server uses both Windows and SQL Server security.
- If the value is 1 it is only setup for Windows Authentication.


To check for SQLServer version
SELECT @@VERSION


To check for SQLServer version (Alternative)
SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,
 SERVERPROPERTY('ProductLevel') AS ProductLevel


To find logins mapped to the "dbo" user in each database
EXEC master.sys.sp_MSforeachdb '
PRINT ''?''
EXEC [?].dbo.sp_helpuser ''dbo'''
Notes:
The dbo which also means database owner, is a user account that has permissions to perform all activities in the database. Members of the sysadmin fixed server role are automatically mapped to dbo account

To get the list of the users
EXEC sys.sp_helpuser


To get the list of database permissions
EXEC sys.sp_helprotect


To get the list of roles membership
EXEC sys.sp_helprolemember


To get the list of database application roles
SELECT name FROM sys.database_principals WHERE type = 'A'


To Check data and log files drives for the current database
SELECT name, type_desc, physical_name, 
 LEFT(physical_name, CHARINDEX( '\', physical_name,0)) AS DriveLetter
FROM sys.database_files


To get the list of linked server and the logins used for linked servers
SELECT * FROM sys.servers


Microsoft SQL Server Logo is the copyright of its respective owner