DDL LOGON event - Audit Logins

I am not sure if this is the right section for this post....

We have SqlServer 2005 with a bunch of databases. To audit logins, I created
DDL trigger on LOGON event to capture the EVENTDATA from a successful login
and INSERT into a table (say, Tbl_AuditLogins) in one of the databases in the
same Instance. Plain vanilla..... simple.

Everything works ok... or so I thought

But last week, as part of rebuilding a particular database (say, DBAdb), on
the same instance, I ran a script file that drops and recreates and goes
through the whole process..... which takes about 10 - 12 mins. But during
this time, for some reason, nobody could login. So after some analysis I
realized that the table (Tbl_AuditLogins) into which this DDL trigger stores
the EVENTDATA info is in 'DBAdb' database. So looks like the DDL trigger
failed as it could not successfully insert EVENTDATA into this table
(Tbl_AuditLogins).

So far... fair..

So I added some code to check if the table (Tbl_AuditLogins) is existing
before trying to INSERT into that table.

But............Doesn't work....

So I added more code to see if the database (DBAdb) is existing before
attempting to INSERT.... if the database is not found or table in the
database is not found....just exit DDL-trigger without failure.

But...... Doesn't work

After spending some time....... analyzing, I realized that may be when the
database is dropped (or the table is dropped) the COMPILED object (of this
DDL trigger) is set to RECOMPILE the next time it runs as the underlying
objects have changed. But the recompile fails as the underlying objects are
not existing..... leaving the TRIGGER in some INVALID state. So the
DDL-trigger bombs..... and no logins are allowed.

How do I over come this? I'd like to see that Logins not interrupted whether
the trigger is successful or not in storing the EVENTDATA.

Options:
1: Make sure the table (Tbl_AuditLogins) or database (DBAdb) is not DROPped
--with a DDL trigger to rollback.
2: Create this table (Tbl_AuditLogins) in the master database. <-- If
possible, I want to avoid this
3: ??

thanks in advance,
_Uday

.

Relevant Pages

  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Changing security authentication type.
    ... i'm thinking that yes in time the sa account ... A new DBA Admin User login would only be useful for SQL Login purposes. ... if appropriate you can add users to the db_owner role in a database. ... Create one or more logins for the applications. ...
    (microsoft.public.sqlserver.security)
  • Re: Help Ive been an idiot
    ... You need to make sure that you readd any logins that were mapped to database ... Jasper Smith (SQL Server MVP) ... > connectionString, CommandType commandType, String commandText, ...
    (microsoft.public.sqlserver.setup)
  • RE: Creating a DB User *not* on current Machine
    ... detach/attach the database to a new server. ... Server.When you move a database from one server that is running SQL Server ... the security identification numbers of the logins in the master ...
    (microsoft.public.sqlserver.security)