Post reply

Auditing the "sa" Account

  • March 2, 2006 at 8:40 am

    #116328

    Hello all,

    I am very new to being a DBA and have been given the task of auditing the use of the "sa" account.

    Currently my company has 32 SQL servers in 16 sites through out the world.  We are aware that the developers have used the "sa" account in there applications.

    I need to perform an audit of the "sa" account and determine what applications are using this account.

    Can anyone help me with a script that would perform this type of audit?

     

    Thank you from a very new DBA

  • March 2, 2006 at 10:45 am

    #624395

    It is difficult to write scripts for the environment that you are not familiar with. But the very first advise will be to use traces.

    Please read my reply at this site on the similar question of 12/14/2005

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=243614

    "SQL Login User"

    I posted a sample script there. You may also want to add a filter for the login name SA. My filter in the script is for database ID.

    Please see for more information:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_trace_1nci.asp

    "How to create a trace"  and related articles:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_227n.asp

     

    Regards,Yelena Varsha

  • March 3, 2006 at 6:33 am

    #624574

    Running a trace will quickly identify frequently used applications that use sa.  However, there are still going to be some obscure, used once every three months, utilities that aren't going to be picked up by your trace. 

    Use your trace to show the need to lock down sa.  It will allow you to identify development groups who are the biggest "offenders" and you can concentrate efforts on cleaning up their code. 

    Then pick a date, warn all development groups and change the sa password on that date.  Make the developers responsible for any applications that break with the change.

    Scott

  • March 3, 2006 at 7:28 am

    #624607

    Thank you both for the information.  I have already started to run the trace.

    I was asked another question by my boss that I would like to ask you.

    When running a trace, is there anyway to determine the IP address or Hostname of the computer that is running the application with the "sa" account?

    I added the hostname column to my trace, but it does not seem to be providing much information.

    Thanks

    Bonn

Viewing 4 posts - 1 through 3 (of 3 total)

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