How to tell what is using the 'sa' account

  • When I inherited our SQL Server databases, the correct 'sa' password was not handed down. From other posts, I'm pretty sure I know how to change the 'sa' password, but what I do not know how to do is determine which (if any) applications, jobs, etc. are using the 'sa' account to connect. I do realize it is bad practice, but wouldn't be surprised if there are applications using the account.

    So... how can I find out what applications, jobs, etc are using the 'sa' account before I change the password and break something?

    Thanks.

  • A logon trigger that records the event to a table should suffice. Dump the ip, hostname, username, event time etc to that table and you would start to get a good idea of how it is being used.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • You can also run a server side trace and filter on login, hostname, ip, Program_name. Run that for a week or so to give you a better idea.

  • Change the password and see who screams!:-P Then you can also fix the security holes in those applications. I'm only partly joking.

  • Jack Corbett (4/13/2011)


    Change the password and see who screams!:-P Then you can also fix the security holes in those applications. I'm only partly joking.

    You're messing me up with that avatar !

  • homebrew01 (4/13/2011)


    Jack Corbett (4/13/2011)


    Change the password and see who screams!:-P Then you can also fix the security holes in those applications. I'm only partly joking.

    You're messing me up with that avatar !

    He's only 1 of about 8 that are now using it (including Mr. Fritchey).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Is this the "Login Auditing" feature that can be enabled from the Server Properties in the Management Studio that you're referring to? Or is this a different method, if so, can you explain how? Thx.

  • Here is a technet article on it:

    http://technet.microsoft.com/en-us/library/bb326598.aspx

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Jack Corbett (4/13/2011)


    Change the password and see who screams!:-P Then you can also fix the security holes in those applications. I'm only partly joking.

    Dont joke like that.... I have done this in the past... and more importantly, I will do it in the future!!! :hehe:

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

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