Thanks again for the reply. I tried your code but still couldn't get the sql to display. By retriveing the whole event data and looking at the event instance schema I worked out I could get what I wanted by getting the "objectname" from the event_instance.
This page helped me a lot: http://msdn.microsoft.com/en-us/library/ms173781%28v=sql.90%29.aspx
Code now looks like the following.(Email wording changed to be more natural):
-- Script to add trigger to alert (via email) when a new login is created on the SQL Server:
IF EXISTS (SELECT * FROM sys.server_triggers
WHERE name = 'ddl_trig_CreateLogin')
DROP TRIGGER ddl_trig_CreateLogin
ON ALL SERVER;
CREATE TRIGGER ddl_trig_CreateLogin
ON ALL SERVER
-- Declare variables
DECLARE @mailSubject Nvarchar(100);
DECLARE @mailBody Nvarchar(MAX);
DECLARE @data XML;
DECLARE @text Nvarchar(max);
DECLARE @user Nvarchar(max);
SET @data = EVENTDATA();
SET @text = @data.value('(/EVENT_INSTANCE/ObjectName)', 'nvarchar(max)');
SET @user = @data.value('(/EVENT_INSTANCE/LoginName)', 'nvarchar(max)');
---- Set the email data
SET @mailSubject = 'New SQL Login Created On: ' + @@SERVERNAME;
SET @mailBody = 'A new login was detected on the SQL Server: <b>' + @@SERVERNAME + '
'User name: <b>' + ISNULL(@text, 'Null User Name') + '
'Added by user: <b>' + ISNULL(@user, 'Null') + '
</b>' + +
'On date: <b>' + CONVERT(nvarchar, getdate(), 13) + '
'Please verify why this login was created!' + '
--Send the mail
@body = @mailBody,
@profile_name = 'DefaultMailProfile',
@body_format = HTML;
Which results in an email being sent when a login called "test" is added to the server:
A new login was detected on the SQL Server: servername
User name: test.
Added by user: user
On date: 16 Nov 2012 13:42:01:760
Please verify why this login was created!
Perfect! I can now get alerted when privileged users create logins on production systems!
Thanks again for your help.