Twitter has provided some great fodder for blog content lately. And twice this week, I've found an excuse to take advantage of the default trace. Tonight @meltondba asked:
I'm trying to find who created a user act in a DB
|
It is true, SQL Server doesn't really keep track of who created objects, such as user accounts in a database. You can get some of this information from the default trace, though, since it tracks EventClass 109 (Audit Add DB User). If you run this code:
USE [master];
GO
CREATE LOGIN bob WITH PASSWORD = 'xyz123';
GO
USE [your_database];
GO
CREATE USER bob;
GO |
You could then use the following query against the default trace to find the machine name and login name for the user responsible:
USE [your_database];
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'your_database'
AND TargetUserName = N'bob'; |
Feel free to add TextData to the column list to see that the trace does not capture the SQL that the user submitted, but it certainly does capture the event. I include the host name here because if you are using SQL authentication or in a data center with shared logins, the login name alone might not be useful. Admittedly, it may also be true that the host name doesn't help either, for example if everyone RDPs to the same box in your data center. But hopefully you have a little more control in your environment.
Going forward, you could do this with a DDL trigger also (as I described in Monday's blog post about tracking index rebuild operations).