November 16, 2012 at 2:41 am
Hi,
I wonder if anyone can point me in the right direction.
I would like a way to be alerted (preferable by email) when a new login is created on a number of databases I manage. A number of people within my team, (the system administrators), have full sysadmin access to the databases and on occasion (when I am not around) have been known to just create logins as members of the sysadmin group when requested by other users. Is there anyway to be alerted by email when a new login is created so that I am aware if this happens and then remove/change the user rights?
I have looked at Audit specifications, but these only write to logs or the windows log. I am happy to write a trigger on a table for example when a new principal is added. Is it ok to write user triggers on system tables?
Thanks.
November 16, 2012 at 2:53 am
Look at creating a DDL trigger for the CREATE LOGIN facet which sends an email via sp_send_dbmail.
Something like the following
CREATE TRIGGER LoginCreateTrigger ON ALL SERVER
FOR CREATE_LOGIN
AS
BEGIN
DECLARE @data XML;
SET @data = EVENTDATA();
EXECUTE MSDB.DBO.sp_send_dbmail @profile_name = , @recipients = '', @subject = 'Login Created Alert', @body = @data, @body_format = 'HTML'
END
November 16, 2012 at 2:59 am
Wonderful! That's what I was after.
Im just having a look at all the DDL events and will definitely be able to play around with these:
http://msdn.microsoft.com/en-us/library/bb522542.aspx
Thanks again.
November 16, 2012 at 4:52 am
Ok, I am getting there, but I've run into a problem. Here is the trigger code I have created, but the problem is the TSQL part of the EVENTDATA is always Null. If I dont put a ISNULL check for it, it nulls the entire message body.
I would like to receive in the mail what new user was created and thought I could do this by presenting the TSQL executed.
Any help would be great.
-- 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;
GO
CREATE TRIGGER ddl_trig_CreateLogin
ON ALL SERVER
FOR CREATE_LOGIN
AS
-- Declare variables
DECLARE @mailSubject Nvarchar(100);
DECLARE @mailBody Nvarchar(MAX);
DECLARE @data = xml;
---- Set the email data
SET @mailSubject = 'New SQL Login Created On: ' + @@SERVERNAME;
SET @mailBody = 'A new login was created on SQL Server: <b>' + @@SERVERNAME + '
</b>' +
'By user: <b>' + ISNULL(@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'), 'Null Login' ) + '
</b>' + +
'The TSql executed to do this was: <b>' +
ISNULL(@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'), 'Null SQL ' )+ '
</b>' +
'At: <b>' + CONVERT(nvarchar, getdate(), 120) + '
' +
'Please verify why this login was created' + '
</b>' ;
--Send the mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'maddave@maddave.com,
@subject=@mailSubject,
@body = @mailBody,
@profile_name = 'DefaultMailProfile',
@body_format = HTML;
November 16, 2012 at 5:01 am
It looks like your not setting @data = EVENTDATA() unless I am being blind and cant see it in the code.
Also if your shredding the TSQLCommand node you need to add in the subnode so it would be
@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)')
Other wise you will get a NULL due to there being no text in root element of TSQLCommand.
November 16, 2012 at 6:09 am
Thanks for this. I was setting @data to the eventdata(), but I just cleared that bit out of my example code by accident! Sorry my fault.
I've added the line
'The TSql executed to do this was: <b>' +
ISNULL(@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)'), 'Null SQL ' )+ '
</b>' +
to the mailbody, but still this comes back as null. This is the email received:
A new login was created on SQL Server: CorrectServername\instance
By user: Correctdomain\correctUser
The TSql executed to do this was: Null SQL
At: 2012-11-16 12:52:41
Please verify why this login was created
I was reading about EVENTDATA() and at a create login event the password being removed for security, but it doesn't mention about the rest of the sql.
Thanks for taking the time to look at this.
November 16, 2012 at 6:16 am
Yep sorry, you need to do all your xml shredding outside of your email block
-- 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;
GO
CREATE TRIGGER ddl_trig_CreateLogin
ON ALL SERVER
FOR CREATE_LOGIN
AS
-- 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/TSQLCommand)[1]', 'nvarchar(max)');
SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
---- Set the email data
SET @mailSubject = 'New SQL Login Created On: ' + @@SERVERNAME;
SET @mailBody = 'A new login was created on SQL Server: <b>' + @@SERVERNAME + '
</b>' +
'By user: <b>' + ISNULL(@user, 'Null Login' ) + '
</b>' + +
'The TSql executed to do this was: <b>' +
ISNULL(@text, 'Null SQL ' )+ '
</b>' +
'At: <b>' + CONVERT(nvarchar, getdate(), 120) + '
' +
'Please verify why this login was created' + '
</b>' ;
--Send the mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'maddave@maddave.com',
@subject=@mailSubject,
@body = @mailBody,
@profile_name = 'DefaultMailProfile',
@body_format = HTML;
November 16, 2012 at 6:49 am
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;
GO
CREATE TRIGGER ddl_trig_CreateLogin
ON ALL SERVER
FOR CREATE_LOGIN
AS
-- 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)[1]', 'nvarchar(max)');
SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', '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 + '
</b>' +
'User name: <b>' + ISNULL(@text, 'Null User Name') + '
</b>' +
'Added by user: <b>' + ISNULL(@user, 'Null') + '
</b>' + +
'On date: <b>' + CONVERT(nvarchar, getdate(), 13) + '
' +
'Please verify why this login was created!' + '
</b>' ;
--Send the mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'maddave@maddave.com',
@subject=@mailSubject,
@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.
November 16, 2012 at 7:11 am
Unsure why your having trouble with the command text
CREATE TRIGGER ddl_trig_CreateLogin
ON ALL SERVER
FOR CREATE_LOGIN
AS
-- Declare variables
DECLARE @mailSubject Nvarchar(100);
DECLARE @mailBody Nvarchar(MAX);
DECLARE @data XML;
DECLARE @text Nvarchar(max);
DECLARE @user Nvarchar(max);
DECLARE @newuser NVARCHAR(MAX);
SET @data = EVENTDATA();
SET @newuser = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(max)');
SET @user = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)');
SET @text = @data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', '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 + '
</b>' +
'User name: <b>' + ISNULL(@newuser, 'Null User Name') + '
</b>' +
'T-SQL was <b>' + ISNULL(@text, 'Null SQL') + '
</b>' +
'Added by user: <b>' + ISNULL(@user, 'Null') + '
</b>' + +
'On date: <b>' + CONVERT(nvarchar, getdate(), 13) + '
' +
'Please verify why this login was created!' + '
</b>' ;
SELECT @mailBody
--Send the mail
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'',
@subject=@mailSubject,
@body = @mailBody,
@profile_name = '',
@body_format = HTML;
I created this and the mail came out with the T-SQL
A new login was detected on the SQL Server: Ants-PC User name: ant2 T-SQL was CREATE LOGIN [ant2] WITH PASSWORD=N'******', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF Added by user: EMEA\anthony.green On date: 16 Nov 2012 14:09:00:210 Please verify why this login was created!
November 16, 2012 at 7:17 am
Hmm, that is strange. I've run your code and get null for the sql part.
Anyway, it's doing what I need for now. If I have some more time, then I will play about some more and see if I can work it out.
Thanks.
November 7, 2014 at 5:51 am
Hi I tried the above query on the MS SQL server database. This created a trigger. After that I could not add any login to server.
Below is the Error message.
"An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Implict conversion from data type xml to nvarchar(max) is not allowed. Use the convert function to run this qury.
the statement has been terminated. (Mocrosoft SQl Server, Error:257)"
Can anyone please help me. This needs to be resolved asap.
May 22, 2019 at 6:23 pm
Is there a way to capture who and what logins got deleted using the same trigger?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply