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 scripts 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:
- Access to an SQL Server database
- DB Roles required: db_reader
To check if guest account is still activeSELECT 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 offSELECT 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 segregatedSELECT 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” loginSELECT 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 settingsSELECT 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 versionSELECT @@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 permission 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 usersEXEC sys.sp_helpuser
To get the list of database permissionsEXEC sys.sp_helprotect
To get the list of roles membershipEXEC sys.sp_helprolemember
To get the list of database application rolesSELECT name FROM sys.database_principals WHERE type = 'A'
To Check data and log files drives for the current databaseSELECT 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 serversSELECT * FROM sys.servers
Leave a Reply