MS SQL Server DB Security Auditing

You can use the command below for ISO 27001 requirements checking.

MS SQL Server Version

SELECT @@version

List of Databases on MS SQL Server

select * from sys.databases

List of All Users on MS SQL Server

SELECT * FROM sysusers where islogin = 1

List of Users that have DBA authorization

sp_helprolemember db_owner

List of Users that have authorization beyond Select command

SELECT a.*, b.* ,*

FROM sys.database_principals a

INNER JOIN sys.database_permissions b ON b.grantee_principal_id = a.principal_id

WHERE b.permission_name not like '%SELECT%'

 

List of users that their passwords are empty

 

select name

from sys.sql_logins

where pwdcompare('', password_hash) = 1

 

 

Check if password and expiration policy is applying to users

 

select name ,is_expiration_checked,is_policy_checked ,*

from sys.sql_logins where is_expiration_checked = 0 or is_policy_checked= 0

 

List of SYNONYMs

 

If you create public synonyms all users can read tables, so synonyms usement is not suitable for data secuirity.

 

select * from sysobjects where xtype = 'SN'