Who let the access open?

Credits: David Fulmer

Getting access to a production database should have a process where security team should also involved. But someone might give access to a user bypassing the process. If this sort of unauthorized users are found having access to the database during your audit reports you surly want to know about who and when. If a user was created on the database wrongly, and you want to know who created it then following script will help:

It will give you HostName from where "Create User ..." was executed and LoginName of the user who executed it and Time when the user was created.

USE [DatabaseName];
GO

DECLARE @trace_id INT
 ,@filename NVARCHAR(4000);

SELECT @trace_id = id
FROM sys.traces
WHERE is_default = 1;

SELECT @filename = CONVERT(NVARCHAR(4000), value)
FROM sys.fn_trace_getinfo(@trace_id)
WHERE property = 2;

SELECT HostName
 ,LoginName
 ,StartTime
FROM sys.fn_trace_gettable(@filename, DEFAULT)
WHERE EventClass = 109
 AND DatabaseName = N'DatabaseName'
 AND TargetUserName = N'UserCreated';