如何在登录 SQL 触发器中获取数据库名称
如何在登录触发器中获取数据库名称
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
相关文章