Post reply

logon audit

  • October 23, 2007 at 1:31 am

    #119117

    Can anyone tell me the best way to audit logons - just a simple way without reams of coding to see who has made a connection to which databases.

    I want to do it like SQL Profiler but want to be able to schedule it. Probably sp_create_trace is the way, but I can't find any simple examples out there!

    thanks

  • October 23, 2007 at 1:38 am

    #744357

    try to implement by adopting some the keys mentioned overhere.....

    http://www.sqlservercentral.com/articles/Monitoring/basicsofc2auditing/1547/

  • October 23, 2007 at 1:39 am

    #744358

    SQL Server 2005 SP2 has introduced logon triggers http://msdn2.microsoft.com/en-us/library/bb326598.aspx. Alternatively, if you do not have SP2 you can use the service broker to implement these like on http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • October 24, 2007 at 2:51 am

    #744811

    I'm not sure there is a "best way".

    Apart from ensuring that you have the correct level of security on your DB.

    For example, you have an app (or more than 1 app) that accesses SQL Server... Now, it's best to use connection pooling....so you use a specific account with strictly limited access...

    Now, from the SQL side you can't easily tell who is accessing, everything should go through the pool....

    From the app side, you could add logging...

    And then, you get a request to make the app available via the web.

    What credentials are you trying to track - and why do you want to do it? Any particular suspects? Are you clear in law?

    You need a compelling reason - other than my boss told me to do it. Backbone required here. Explain the consequences.

    You won't need "reams" of code. But you will need lots of code in lots of places. And you WILL miss places...

    2005 does have some interesting features, but in the real world most stuff is on 2000 and earlier.

  • October 24, 2007 at 9:03 am

    #745001

    One really nice feature of SQL Profiler 2005 is that it will let you export the script trace definition to a script for SQL 2000 or 2005.

    Generate your trace using profiler then go to File, Export, Script Trace Definition. Once its exported you have to make a few minor changes but from there its fairly easy to work with. And you get to build the original script (you can't edit it with the interface as far as I know) using the nice easy profiler interface :).

    Kenneth Fisher

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • October 24, 2007 at 9:07 am

    #745004

    I can't try that, I'm locked into 2000 - but as I use pooled access how could your sol help me profile individuals?

  • October 24, 2007 at 9:16 am

    #745007

    Assuming your company will let you I would go ahead and switch over to the 2005 tools. You can interface with 2000 just fine with the exception of DTS. You will want to keep the 2000 tools available for any DTS work you need to do. Also the scripting options are a bit better (in my opinion) in 2000.

    As far as the profiling goes you can get not just the userid that logged in but the machine they are logging in from. Assuming that everyone logs in from their own machine you can track it that way. Fair warning you won't ALWAYS get this information so I would do some testing using profiler and see what results you get.

    If this doesn't work and you are worried about the connections from an application you can write a login screen up front and require your users to log in. Then create a table that stores not only each time they log in (and the application user id) but also include the SPID that they logged in under. Then with a little bit of work you should be able to tie your logging table to the profiler information.

    Kenneth Fisher

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • October 24, 2007 at 9:31 am

    #745017

    Thanks, I'll give it a bash once I get some freetime. Great suggestion. Have bookmarked this thread.

    It's great to have people with positive contributions to make. Moderators, please give this person an additional few points at you discretion....

  • October 24, 2007 at 6:12 pm

    #745168

    Kenneth Fisher (10/24/2007)

    One really nice feature of SQL Profiler 2005 is that it will let you export the script trace definition to a script for SQL 2000 or 2005.

    This can be done with the SQL Profiler in SQL Server 2000 as well.

    File | Script Trace | For SQL Server 2000

    K. Brian Kelley
    @kbriankelley

Viewing 9 posts - 1 through 8 (of 8 total)

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