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