問題描述
如何在登錄觸發器中獲取數據庫名稱
How to get Database Name in a Logon Trigger
嘗試了幾個tsql代碼
tried several tsql code
CREATE TRIGGER tr_stop_excel_users
ON ALL SERVER FOR LOGON
AS
BEGIN
IF (SELECT DB_NAME() FROM sys.databases) = 'TESTDB' and ORIGINAL_LOGIN() <> N'xx\xxxxxxx' AND APP_NAME() LIKE '%Microsoft Office%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%
ROLLBACK;
END
高于 DB_NAME 總是產生 master
我正在嘗試在登錄觸發器中獲取數據庫名稱，但它無法以任何方式工作，我嘗試......低于 DB_NAME 總是 master......我正在嘗試這里要做的就是屏蔽使用excel查詢TESTDB數據庫的用戶....
I am trying to get Database Name in a Logon Trigger and its not working in any way I try….below the DB_NAME is always master…what I am trying to do here is to block users who are using excel to query the TESTDB database….
推薦答案
如果您在 LOGON 觸發器中使用 Db_Name，您將獲得默認的數據庫名稱.因此，當您獲得 master 時，它顯示登錄的默認數據庫是 master.
If you are using Db_Name in LOGON trigger, you will get the default database name. So as you get the master, it shows that login's default database is master.
如果您需要獲取其他名稱，則需要在應用程序中更改連接字符串，或在 SSMS 登錄提示屏幕中提供數據庫名稱，或任何其他可以提供數據庫名稱的地方(轉到 ??Options/SSMS 登錄提示屏幕中的連接屬性/連接到數據庫)
If you need to get other names, you need to change your connection string in application, or provide database name in SSMS Login prompt screen, or any other places where you can provide the database name(Go to Options/Connection Properties/Connect to Database in Login prompt screen in SSMS)
如果您不提供數據庫名稱，登錄將連接到其默認數據庫，即在Security/Login/Default Database
If you do not provide database name, login will connect to its default database, that is set in Security/Login/Default Database
適合您的解決方案
使用 Db_Name 對您來說不是一個好的選擇，我建議您使用 APP_NAME 函數代替.
Using Db_Name is not a good option for you, I recommend you to use APP_NAME function instead.
StackExchange 中討論的相同問題:https://dba.stackexchange.com/questions/40155/prevent-users-from-using-power-pivot-excel-connections-to-a-database
Same problem discussed in StackExchange: https://dba.stackexchange.com/questions/40155/prevent-users-from-using-power-pivot-excel-connections-to-a-database
這篇關于如何在登錄 SQL 觸發器中獲取數據庫名稱的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！