如何在登录 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.

如果您需要获取其他名称，则需要在应用程序中更改连接字符串，或在 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