Security trigger when user is added to a database

Question

How to implement a trigger mechanism in SQL Server that does a specific action such as send an email when a new user is added to a database.

The idea is that, there is a Data Warehouse and everyone, i.e., IT, Performance team, DW team has access and be able to add users to databases.

Now the problem is, we would like the user to contact our department managers prior to the user is being granted permission to use our database.

At the moment, we do daily check by expanding the Security > Users section to see if the listed users are the intended ones, which brings a problem to what if a user was added and did their query and then was removed.

One could use the MS SQL Profiler, however, it is only for query audits and not for who has access (please let me know if there is a way for this in Profiler).

In my opinion, may be write a PowerShell script and have it executed every minute randomly to see if something is different and then email. However, this idea seems to abuse the server performance. Please advice and thank you in advance.

Answer 1

Thank you, Venkataraman (https://stackoverflow.com/users/634935/venkataraman-r) for suggesting creating a DDL Trigger that could interrupt the process of user creation. The DDL Trigger instructions can be found on Microsoft Docs page and / or on Modern Database Management 13th Edition book, DDL Trigger section (Hoffer, Ramesh and Topi, 2022), please see the end of this content to find these links and details. As suggested, I have created a server level DDL Trigger using the following SQL query and tested to create a user on [<instance>] > Security > Logins.

The trigger worked and prevented me from adding a user, at a server level, which displayed the below error.

However, since I would like this to work on a specific database, using the above method is not really a solution. Therefore, I changed the “All Server” part of the query to “DATABASE”. However, this threw an error stating “The specified event type(s) is/are not valid on the specified target object”, please see the below screenshot for more details.

Next test is to change the query slightly to drop the aforementioned query, using “drop trigger users_security_trigger on all server” and then create a trigger for database and replace the “create_login” with “create_user”, like the below screenshot.

This query executed successfully and prevented me from creating a user as shown in the below screenshot.

This will also prevent when a user with permission to try add “User Mapping” to the database, therefore, this query works as intended.

Email configuration:

/*
    -- activate database email
    sp_configure 'Database Mail XPs', 1;
    go
    reconfigure
    go
*/

-- fill out the below section for the email account
declare @email_address nvarchar(128) = N'your email address';
declare @replyto_address nvarchar(128) = N'your reply to email address';
declare @password nvarchar(128) =  N'your email password';

-- leave the below if using Microsoft
declare @mailserver_name nvarchar(128) = N'smtp.office365.com'; -- for Microsoft default "smtp.office365.com"
declare @mailserver_type nvarchar(128) = N'SMTP'; -- for Microsoft default "SMTP"
declare @port int = 587; -- for Microsoft default port 587

-- you can leave the below as default
declare @enable_ssl bit = 1; -- default = 1
declare @description nvarchar(256) = concat(N'Email used for sending outgoing reports using "', @email_address, '"') ;


-- create profile, account and profile account
/* 1. create profile */ 
if not exists(
select * from msdb.dbo.sysmail_profile where  name = @email_address
) begin exec msdb.dbo.sysmail_add_profile_sp @email_address,  @description; end;

/* 2. create account */ 
if not exists(
select * from msdb.dbo.sysmail_account where  name = @email_address
) begin 
exec msdb.dbo.sysmail_add_account_sp 
@email_address, @email_address, @email_address, @replyto_address, @description,
@mailserver_name, @mailserver_type, @port, @email_address, @password, 0, @enable_ssl,null;
end;
    
/* 3. create account profile */ 
if not exists(
select * from msdb.dbo.sysmail_profileaccount pa  
    inner join msdb.dbo.sysmail_profile p on pa.profile_id = p.profile_id  
    inner join msdb.dbo.sysmail_account a on pa.account_id = a.account_id   
    where p.name = @email_address and a.name = @email_address
) begin  
exec msdb.dbo.sysmail_add_profileaccount_sp null,@email_address, null, 
@email_address, @sequence_number =1; 
end;

/* 4. check if profile is created */ exec msdb.dbo.sysmail_help_profile_sp;
/* 5. check if account is created */ exec msdb.dbo.sysmail_help_account_sp;
/* 6. check if profile account is created */ exec msdb.dbo.sysmail_help_profileaccount_sp;

/*
-- deletion if needed, uncomment as necessary

/* 1. delete profile account */ 
if exists(
    select * from msdb.dbo.sysmail_profileaccount pa  
    inner join msdb.dbo.sysmail_profile p on pa.profile_id = p.profile_id  
    inner join msdb.dbo.sysmail_account a on pa.account_id = a.account_id   
    where p.name = @email_address and a.name = @email_address
) begin  exec msdb.dbo.sysmail_delete_profileaccount_sp  null, @email_address; end;

/* 2. delete account */ 
if exists(
select * from msdb.dbo.sysmail_account where  name = @email_address
) begin exec msdb.dbo.sysmail_delete_account_sp  null, @email_address; end;

/* 3. delete profile*/
if exists(
select * from msdb.dbo.sysmail_profile where  name = @email_address
) begin exec msdb.dbo.sysmail_delete_profile_sp null, @email_address; end;
*/

-- send a test email
exec msdb.dbo.sp_send_dbmail
@profile_name = <@email_address ‘your email address’>,
@recipients = 'send to address',
@subject = 'Automated message',
@body = 'email successfully configured';

go

Finally, when a user from IT, DW or whoever has the privilege permission to add / create a user on your database, you can use the below query. This query will not interrupt from adding the user, it will just notify you via email.

use [database]
go

alter trigger users_security_trigger on database for create_user as 
begin

declare @body varchar(500)= 
'User ' +
lower(quotename(eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(300)'))) + 
' was added to your database by ' + 
lower(quotename(system_user));

exec msdb.dbo.sp_send_dbmail
@profile_name = <@email_address ‘your email address’>,
@recipients = 'send to address',
@subject = 'A new user was created on your database',
@body = @body;

end;

References

1. Hoffer, J., Ramesh, V. and Topi, H., 2022. Modern database management. 13th ed. Harlow, United Kingdom: Pearson Education Limited, pp.281-284.
2. Microsoft Docs page (DDL Triggers https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver16)
3. https://dba.stackexchange.com/questions/228577/how-to-deny-permissions-for-newly-created-user-in-ddl-trigger
4. https://www.sqlshack.com/configure-database-mail-sql-server/
5. https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/database-mail-stored-procedures-transact-sql?view=sql-server-ver16

Answer 2

you can create DDL trigger with DDL event as CREATE USER and you can send email accordsingly. You can create mail profile and send mail using sp_send_dbmail

CREATE TRIGGER NewUserAdditionAlert
ON DATABASE   
FOR CREATE_USER  
AS   
BEGIN
DECLARE @create_user_stmt NVARCHAR(2000) = (SELECT EVENTDATA().value  
        ('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'));

EXEC msdb.dbo.sp_send_dbmail @recipients='test@test.com',  
    @subject = 'New User is trying to get created',  
    @body = @create_user_stmt ;
   
   RAISERROR ('You cannot create user in database. Contact test@test.com for getting access to database!', 10, 1)  
   
   ROLLBACK  
END
GO  

• DDL events - https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events?view=sql-server-ver16

• DDL triggers - https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver16