问题描述
如何在登录触发器中获取数据库名称
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.
如果您需要获取其他名称,则需要在应用程序中更改连接字符串,或在 S**S 登录提示屏幕中提供数据库名称,或任何其他可以提供数据库名称的地方(转到 ??Options/S**S 登录提示屏幕中的连接属性/连接到数据库)
If you need to get other names, you need to change your connection string in application, or provide database name in S**S 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 S**S)
如果您不提供数据库名称,登录将连接到其默认数据库,即在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