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

这篇关于如何在登录 SQL 触发器中获取数据库名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！