This page contains some SQL queries that can be used on an SQL Server to test some parts of the security configuration.

• select name from sys.sql_logins where type = 'S' and is_policy_checked <> '1'
  This query is only usefull when the server is configured to use 'Mixed authentication'. Furthermore, this query is only useful when SQL Server has been installed on Windows Server 2003 or later.
  If this query returns any rows, then this means that all the listed accounts are accounts defined in SQL Server for which no password complexity has been enforced.
• select name from sys.sql_logins where type = 'S' and is_expiration_checked = 0
  This query is only usefull when the server is configured to use 'Mixed authentication'. Furthermore, this query is only useful when SQL Server has been installed on Windows Server 2003 or later.
  If this query returns any rows, then this means that all the listed accounts are accounts defined in SQL Server for which the password doesn't expire.
• exec sp_helpdb
  This query will return all databases that exist within the SQL Server. You should check whether any demonstration or sample databases are available on the server. The following is a list of standard sample databases:
  • AdventureWorks
  • AdventureWorksDW
  • AdventureWorksAS
  • Northwind
  • pubs
• select count(name) from sys.sql_logins where password_hash is null and name='sa'
  This query will check whether a password has been assigned to the sa account. If the query returns a row, then this means that the sa account has a blank password.
• use master
exec xp_loginconfig 'audit level'
• select SUSER_NAME('1') 'Name'
• exec sys.xp_loginconfig 'login mode'