2

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

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62
Sam
  • 21
  • 2
  • 1
    `Logon` is a server operation, not a database operation. Unless the connection string contains an `Initial Catalog` setting, after logon the connection switches to the user's default database. Most developers use `master` – Panagiotis Kanavos May 29 '19 at 10:53
  • 1
    In any case what you tried will just throw an error, and probably lock out everyone - `ROLLBACK` doesn't close connections nor does it roll back *future* transactions. A badly written LOGON trigger [can be evil](https://blog.sqlauthority.com/2018/04/14/sql-server-be-careful-with-logon-triggers-dont-use-host_name/) and throw for *every single logon*, thus preventing everyone from using the database – Panagiotis Kanavos May 29 '19 at 10:59
  • 1
    You probably don't need a trigger *at all*. Just *don't* give everyone access to every database. Give access to the `TestDB` database only to the `xx\xxxxxxx` account. The `APP_NAME()` check is useless. It won't prevent people from using whatever client they want. They may even use VBA to connect to the database or specify the application name as a connection string parameter. – Panagiotis Kanavos May 29 '19 at 11:03
  • 1
    Finally, `AND` takes precedence over `OR` which means the database, user checks apply only to connections that specify `Microsoft Office` as their name. **Every other user** that tries to use `Excel` or `Access` as their application names, to connect to *any* database will get an error. That includes `xx\xxxxxxx` – Panagiotis Kanavos May 29 '19 at 11:05
  • [`DB_NAME()`](https://learn.microsoft.com/en-us/sql/t-sql/functions/db-name-transact-sql?view=sql-server-2017) returns the name of the current database if no argument is specified. `SELECT DB_NAME() FROM sys.databases` returns that value _for each row_ in `sys.databases`, not just a single value. That makes the comparison with `'TESTDB'` a bit perplexing. – HABO May 29 '19 at 11:12
  • Aside from the other caveats with logon triggers (always test on a local instance first, in case you make an error that makes the instance unreachable!) you can't do this (reliably) using a logon trigger. It is always possible for a user to connect to a database they have permission to connect to, then issue a `USE` statement for another database. This does not trigger a second logon event, and in fact is how many client libraries connect (use the user's default database, then switch), though I do not recall if the (OLE DB/ODBC) libraries Excel uses also work this way. – Jeroen Mostert May 29 '19 at 11:21
  • If your goal is to stop office workers from connecting to a particular instance because they'll frequently (unwittingly) issue queries that will tank performance (believe me, I've been there) your best bet is not a logon trigger, but to actually make the server unreachable. Set up a secondary instance with replication or periodic backup restores that they can use/abuse to their heart's content. Alternatively, you can use things like Resource Governor pools to restrict the amount of time/memory they can use, which is less drastic than cutting them off completely. – Jeroen Mostert May 29 '19 at 11:31

1 Answers1

0

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

Vahid Farahmandian
  • 6,081
  • 7
  • 42
  • 62