Sql Server – jstexasdba

I came across an environment recently that had multiple domains with particular trust levels between the them. I then, found out, that when deploying projects via Octopus, there was a project step that was creating a windows login from one domain to another because Octopus had admin permissions across the board (Oh yes, I already know what you are thinking). My initial thought was to contact network team and get an explanation on why the trust levels, however what would that really solve for the immediate need? I then wanted to investigate all the permissions Octopus should truly have, however I did not have time to investigate all of the appropriate permission that would be required. I thought, what a perfect chance to start my own personal challenge of #100daysofcode from twitter.

How could I protect the SQL environment in each domain to ensure that never happens again? How can I ensure that when deploying via Octopus, the individual doing the deployment can be notified of the failure in a clear and concise way? I then stepped into a whelm that I have not touched in quite sometime, triggers, yes I said it, triggers. More specifically DDL triggers. I have had multiple reasons why I have not had to deal with triggers too much, but finally I had enough of this and had to put my foot down as results wanted, were not being driven.

What can DDL triggers do for you? DDL triggers operate on CREATE, ALTER, DROP, and other DDL statements and stored procedures that perform DDL-like operations. They are used to perform administrative tasks and enforce business rules that affect databases. They apply to all commands of a single type across a database, or across a server.

I currently utilize extended events for a lot of auditing and these items are always recorded and emailed the following morning for audit review. I finally got tired of waiting and wanted an email alert immediately, of when this particular event occurred and want to ensure that Octopus also recorded the alert to notify the individual doing the deployment.

Here is the script I came up with:

CREATE TRIGGER ddl_trig_triedcrossdomainlogin
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 windows login was tried to be created on: ' + @@SERVERNAME;
   SET @mailBody = 'A new windows login was detected to be created on SQL Server from another domain: <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 attempted to be created!' + '
   </b>' ;

   --Send the mail
   EXEC msdb.dbo.sp_send_dbmail
      @recipients=N'',         --put your recipients list here
      @subject=@mailSubject,
      @body = @mailBody,
      @profile_name = '',      --put your sql profile name here
      @body_format = HTML;

IF @user like ('%Octopus%') --This would be the login that the application is running under
and 
@newuser like ('%DEV%')     --This would be the account that the application is trying to create a login for
 BEGIN
    PRINT 'You are not allowed to create a dev login in this environment.' 
    ROLLBACK;
  END
GO

ENABLE TRIGGER ddl_trig_triedcrossdomainlogin

CREATE TRIGGER ddl_trig_triedcrossdomainlogin

ON ALL SERVER

FOR CREATE_LOGIN

-- 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 windows login was tried to be created on: ' + @@SERVERNAME;

SET @mailBody = 'A new windows login was detected to be created on SQL Server from another domain: ' + @@SERVERNAME + '

' +

'User name: ' + ISNULL(@newuser, 'Null User Name') + '

' +

'T-SQL was ' + ISNULL(@text, 'Null SQL') + '

' +

'Added by user: ' + ISNULL(@user, 'Null') + '

' + +

'On date: ' + CONVERT(nvarchar, getdate(), 13) + '

' +

'Please verify why this login was attempted to be created!' + '

' ;

--Send the mail

EXEC msdb.dbo.sp_send_dbmail

@recipients=N'', --put your recipients list here

@subject=@mailSubject,

@body = @mailBody,

@profile_name = '', --put your sql profile name here

@body_format = HTML;

IF @user like ('%Octopus%') --This would be the login that the application is running under

and

@newuser like ('%DEV%') --This would be the account that the application is trying to create a login for

BEGIN

PRINT 'You are not allowed to create a dev login in this environment.'

ROLLBACK;

END

ENABLE TRIGGER ddl_trig_triedcrossdomainlogin

This is what Octopus looks like:

Tag: Sql Server

Octopus and a reason to use DDL Triggers