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.
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