permission auditing

Question

Hi All,

last week we found that, one database user permission has been revoked. Is their any chance to audit this on sql server. I m planning to set this.

Thanks,

---

Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

Answer 1

hi,

The Audit Change Database Owner event class occurs when you use the ALTER AUTHORIZATION statement to change the owner of a database, and the permissions required to do that are checked.

pls visit :

http://msdn.microsoft.com/en-us/library/ms178118.aspx

---

Ahsan Kabir

Answer 2

Yes this is very much possible. There are variety of way to achieve this task i.e.

1. DDL Trigger
2. Event Notification
3. AUDIT Specification

Here is the sample code of the DDL trigger:

CREATE TABLE AUDIT (EventInfo xml)
CREATE TRIGGER ddl_trig_audit_security_events 
ON ALL DDL_SERVER_SECURITY_EVENTS,DDL_DATABASE_SECURITY_EVENTS
FOR CREATE_DATABASE 
AS 
    INSERT INTO AUDIT (EventInfo)
    SELECT EVENTDATA()
GO

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com