Alerting when a login is created

  • Maddave

    SSCertifiable

    Points: 6826

    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.

  • anthony.green

    SSC Guru

    Points: 112214

    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

  • Maddave

    SSCertifiable

    Points: 6826

    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.

  • Maddave

    SSCertifiable

    Points: 6826

    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;

  • anthony.green

    SSC Guru

    Points: 112214

    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.

  • Maddave

    SSCertifiable

    Points: 6826

    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.

  • anthony.green

    SSC Guru

    Points: 112214

    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;

  • Maddave

    SSCertifiable

    Points: 6826

    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.

  • anthony.green

    SSC Guru

    Points: 112214

    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!

  • Maddave

    SSCertifiable

    Points: 6826

    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.

  • morampudi.mukesh

    SSC Rookie

    Points: 41

    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.

  • Admingod

    SSCertifiable

    Points: 5664

    Is there a way to capture who and what logins got deleted using the same trigger?

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply