SQL Server Audit

--Auditing Scripts

1). Find Sysadmin accounts

USE master

go

SELECT name FROM syslogins WHERE sysadmin = 1

2). find logins account that are not sysadmin

USE master

go

SELECT name FROM syslogins WHERE sysadmin = 0

3). Find disabled accounts

select * from sys.server_principals where is_disabled = 1

4).

select name, hasaccess

from syslogins where hasaccess = 0

5). Query to find sql login where password policy applied.

select name, is_policy_checked

from sys.sql_logins

6). Get user permission of database user

SELECT Us.name AS username, Obj.name AS object, dp.permission_name AS permission

FROM sys.database_permissions dp

JOIN sys.sysusers Us

ON dp.grantee_principal_id = Us.uid

JOIN sys.sysobjects Obj

ON dp.major_id = Obj.id

where us.name like '%'