SQL Server 2005 Trip Wires

Security in SQL Server 2005: Webcast 2-20-2006

Matt Hester's WebLog — Thu, 23 Feb 2006 04:27:51 GMT

Thank you for attending the web cast on SQL Server 2005 Security, as promised here is the scrubbed Q/A...

re: SQL Server 2005 Trip Wires

Keith Combs — Wed, 23 Nov 2005 23:29:04 GMT

FYI, someone asked how do you drop the trigger since we are using the trigger for rollbacks. See http://channel9.msdn.com/ShowPost.aspx?PostID=139337#139337 for my answer!!!

re: SQL Server 2005 Trip Wires

Keith Combs — Tue, 22 Nov 2005 22:30:30 GMT

Hmmmm... I'll put that on my list of things to do. I'm working on an encryption post right now. After I get done with it, I'll see what I can do. Cheers!!!

re: SQL Server 2005 Trip Wires

Tim Toennies — Tue, 22 Nov 2005 21:47:43 GMT

One thing we'd like to be able to control is not just who has access to a database but how. It would have been very desirable to use the DDL_Server_Level_Event against the Logon event but that's not the case. However...I this can be accomplished with the WMI provider and the login server event & I've attempted to create some code to do this but frankly I've stumbled. I would be very grateful if you would you do the following:

1. Create a script that designs a WMI trigger to recognize a successful login.
2. Have this trigger fire a job that runs an sp_who type query (or uses a new dm view) to find the application name. If the application name is not permitted against the target database it kills the SPID.

Thanks very much for considering, would be a great blog entry!!