How to get Database Name in a Logon SQL Trigger

Question

How to get Database Name in a Logon Trigger

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

above the DB_NAME always yields master

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….

Answer 1

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.

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)

If you do not provide database name, login will connect to its default database, that is set in Security/Login/Default Database

Solution for you

Using Db_Name is not a good option for you, I recommend you to use APP_NAME function instead.

Same problem discussed in StackExchange: https://dba.stackexchange.com/questions/40155/prevent-users-from-using-power-pivot-excel-connections-to-a-database