Sunday, October 7, 2012

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'

No comments:

Post a Comment

span.fullpost {display:inline;}