如何在 SQL Server 中實作觸發機制，當新用戶添加到資料庫時執行特定操作，例如發送電子郵件。
這個想法是，有一個資料倉庫，每個人，即 IT、性能團隊、DW 團隊都可以訪問并能夠將用戶添加到資料庫。
現在的問題是，我們希望用戶在獲得使用我們資料庫的權限之前聯系我們的部門經理。
目前，我們通過展開“安全”>“用戶”部分進行日常檢查，以查看列出的用戶是否是預期的用戶，這會給添加用戶并進行查詢然后被洗掉的情況帶來問題。
可以使用 MS SQL Profiler，但是，它僅用于查詢審核，而不適用于有權訪問的人（如果 Profiler 中有此方法，請告訴我）。
在我看來，可能是撰寫一個 PowerShell 腳本并讓它每分鐘隨機執行一次，看看是否有什么不同，然后通過電子郵件發送。但是，這種想法似乎濫用了服務器性能。請提前咨詢并感謝您。
uj5u.com熱心網友回復：
您可以使用 DDL 事件作為 CREATE USER 創建 DDL 觸發器，并且可以相應地發送電子郵件。
觸發器起作用并阻止我在服務器級別添加用戶，該用戶顯示以下錯誤。
但是，由于我希望它適用于特定資料庫，因此使用上述方法并不是真正的解決方案。因此，我將查詢的“所有服務器”部分更改為“資料庫”。但是，這會引發錯誤，指出“指定的事件型別在指定的目標物件上無效”，請參閱下面的螢屏截圖了解更多詳細資訊。
下一個測驗是稍微更改查詢以洗掉上述查詢，使用“drop trigger users_security_trigger on all server”，然后為資料庫創建觸發器并將“create_login”替換為“create_user”，如下面的螢屏截圖。
此查詢成功執行并阻止我創建用戶，如下面的螢屏截圖所示。
這也將阻止有權嘗試將“用戶映射”添加到資料庫的用戶，因此，此查詢按預期作業。
郵箱配置：
/*
-- 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
最后，當來自 IT、DW 或任何有權在您的資料庫上添加/創建用戶的用戶時，您可以使用以下查詢。此查詢不會中斷添加用戶，它只會通過電子郵件通知您。
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 DRS database',
@body = @body;
end;
參考
- Hoffer, J.、Ramesh, V. 和 Topi, H.，2022 年。現代資料庫管理。第 13 版。英國哈洛：培生教育有限公司，第 281-284 頁。
- Microsoft Docs 頁面（DDL 觸發器https://docs.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://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/database-mail-stored-procedures-transact-sql?view=sql-server-ver16
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/487004.html
上一篇：查找在日期a和b之間更改了某個型別列的數量（int）的每個用戶
下一篇：3個或更多表之間的一對多關系