0

below the Database name is always blank

I am trying to get Database Name in a Logon Trigger using EVENTDATA() but its not working and it appears like the EVENTDATA() is not working at all, I am trying to block users who are using excel to query the 'TestDB'. Can someone tell me what I am doing wrong with this code.

CREATE TRIGGER tr_block_excel_users ON ALL SERVER FOR LOGON AS

declare @data XML declare @DatabaseName as varchar(128)

SET @data = EVENTDATA(); set @DataBaseName = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(128)')

BEGIN IF (select @DatabaseName) = 'TestDB' and ORIGINAL_LOGIN() <> N'xx\xxxxxxxex' AND APP_NAME() LIKE '%Microsoft Office%' OR APP_NAME() LIKE '%EXCEL%' ROLLBACK; END

I expected the name of the database using EVENTDATA(), but getting a blank database name,

Sam
  • 21
  • 2
  • For an [EVENT_INSTANCE_LOGON](http://schemas.microsoft.com/sqlserver/2006/11/eventdata/events.xsd) there is no `DatabaseName`. It's an event on server level not on database level. – sticky bit May 31 '19 at 20:10
  • How do I get the event at the database level?..Thanks – Sam May 31 '19 at 20:16
  • Possible duplicate of [How to get Database Name in a Logon SQL Trigger](https://stackoverflow.com/questions/56358826/how-to-get-database-name-in-a-logon-sql-trigger) – Vahid Farahmandian Jun 01 '19 at 08:49
  • @Sam You have asked exactly the same question in this thread: https://stackoverflow.com/questions/56358826/how-to-get-database-name-in-a-logon-sql-trigger/56359288#56359288 please avoid asking ONE question, multiple times – Vahid Farahmandian Jun 01 '19 at 08:49
  • No, the sql is different for this trigger to get the answer that works,, – Sam Jun 01 '19 at 10:16

1 Answers1

0

If you are trying to just get the database and load it into the variable you can try:

 SET @DatabBaseName = SELECT DB_NAME()
Wes Palmer
  • 880
  • 4
  • 15