Post reply

Is there a Logon trigger at the database level?

  • May 24, 2011 at 8:49 am

    #238074

    Been running it at the server level (which always returns master as the database) and now need to drill down a bit more to which database.

    Hoping somebody has an idea as I don't want to use profiler or run a trace.

    thanks.

  • May 24, 2011 at 9:41 am

    #1329174

    No. Logon is a server-level event, which is why the trigger is only at the server level. What are you trying to do? A DDL trigger at the database level may still apply.

    K. Brian Kelley
    @kbriankelley

  • May 24, 2011 at 10:13 am

    #1329211

    The best you can do is lookup the login's default database but there is no guarantee that the connection string did not provide an alternate/explicit database name to begin in.

    I was curious to know if SQL 11 ("Denali") would offer logon triggers for "contained databases" but its still server-level...however there is more info available if you do use contained databases and could satisfy what you are looking for next time around:

    http://blogs.msdn.com/b/sqlsecurity/archive/2010/12/06/contained-database-authentication-how-to-control-which-databases-are-allowed-to-authenticate-users-using-logon-triggers.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 25, 2011 at 1:45 am

    #1329512

    Thanks for the replies.

    We are trying to get a handle on who is using Office applications to connect to our databases.

    Users have an application that connects to several databases using AD group authentication which then subsequently allows access via Excel / Access.

    Once the analysisis complete we can then build a white list of user / appplication / PC for specific users and block everybody else.

    Just seems a pain you can't do this at the database level but you can at the server level.

  • May 25, 2011 at 8:53 am

    #1329723

    jasonmorris (5/25/2011)

    Thanks for the replies.

    We are trying to get a handle on who is using Office applications to connect to our databases.

    Users have an application that connects to several databases using AD group authentication which then subsequently allows access via Excel / Access.

    Once the analysisis complete we can then build a white list of user / appplication / PC for specific users and block everybody else.

    Just seems a pain you can't do this at the database level but you can at the server level.

    I could see there being a different approach in Oracle or MySQL (from what I know of those platforms) since the notion of a catalog and an instance are somewhat merged.

    My two cents: it makes sense in SQL Server to have the event at the server level when I consider that databases do not handle authentication duties. What event would be raised at the database level and what would happen during cross-database calls? At the point where you're interested in the event being raised the database is already down the path involved in determining whether the caller is authorized to access an object, not checking if the caller has access to the server.

    As an aside, here is another thread going on regarding logon triggers that has the makings of a solution sprinkled into int that is along the lines of what you're wanting:

    http://www.sqlservercentral.com/Forums/Topic1114110-359-1.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • May 25, 2011 at 9:07 am

    #1329736

    Many thanks for the link.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply