SQL Server Performance

URGENT....security of db

Discussion in 'Performance Tuning for DBAs' started by bzeebee, May 17, 2004.

  1. bzeebee New Member

    I want to monitor all user activity like sql's executed , tables created and dropped from the enterprise manager....and other update sqls executed against the production database

    Is it possible....?
    If yes ...HOW???


    Please help!!!!
    -Bzeebee
  2. Raulie New Member

    Yes it is possible you can do this using profiler. Use the TSQL, Strored Procedures event classes SQL<img src='/community/emoticons/emotion-7.gif' alt=':s' />tmtcompleted. To monitor Table creates and drops use the Objects event class example Object:Created, Object<img src='/community/emoticons/emotion-2.gif' alt=':D' />eleted. Adjust the columns and filters you want. You can zero in the perpatrator this way. Good Luck.
  3. Luis Martin Moderator

    Well, first only sa must use Enterprise Manager.
    Anyway, run Profiler and include all events and columns you need, specially Ntusername and loginUsername, with that you will see all who had sa permissions and who did it.
    If you are the only administrator, then change sa password and wait for phone call.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. Raulie New Member

    Not necessarily, you don't need to be just sa to use Enterprise Manager. However you do need a degree of administrative rights to perform some of the actions you report like create and drop statments. Also these statements dont just have to come from Enterprise Manager they can even be executed from a web browser i.e SQL Injection Attacks.
  5. derrickleggett New Member

    Well, first only sa must use Enterprise Manager.

    ?? What do you mean by this Luis? I hate the tool, but many people without sa in our organization use this.

    If you are the only administrator, then change sa password and wait for phone call.

    I agree with this, although approaching it this way would probably get him fired. Our sa password is long, cryptic, changes periodically on no given schedule, and is only in one very protected directory. bzeebee, what's making you think you have a security problem? I'm assuming you posted with that title for a reason.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  6. satya Moderator

    As Derrick suggested the single most important task is to ensure that a strong password protects your "sa" account (and your other accounts as well!). Simply open up Enterprise Manager and drill down to the "Logins" selection of the database you're concerned with.

    Next, take a look at the services you're running on the machine that hosts your database server. If you find extraneous services running, remove them. They're only adding unnecessary complexity to your Internet presence that could possibly introduce additional vulnerabilities to your system security.

    Refer tohttp://www.sqlsecurity.com website for all kinds of security information and deploying tools to combat any issues.

    Lastly, I suggest to use QUERY ANALYZER (mostly) for any kind of administrative activities against the database rather than the EM.

    Review thishttp://www.winnetmag.com/Windows/Article/ArticleID/38737/38737.html link about importance of tools.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. Luis Martin Moderator

    "?? What do you mean by this Luis? I hate the tool, but many people without sa in our organization use "

    I Answer with a question, to do what?

    May be is another topic to discuss.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  8. satya Moderator

    quote:Originally posted by LuisMartin

    Well, first only sa must use Enterprise Manager.
    If you are the only administrator, then change sa password and wait for phone call.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

    I believe Luis is mentioning with regard to admin activities using EM with SA.
    Though we do not have control to disallow users to use EM, as long as the security is tighetened on the database, normal users may not be able to manipulate any information on the database server.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Luis Martin Moderator

    Yes, that's what I mean.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  10. Raulie New Member

    Well Bzeebee hasn't replied to any of our replies so I hope it's not a bad sign :| . However I dont take anyones cries for help lightly, but Bzeebee's posts alway include URGENT in the subject line.
  11. Luis Martin Moderator

    Lazy, take it easy, I don't have statistics but at least 30% post are in this way.
    May be Bzee resolve the problem with or without our help, is ok, I'm shure all of us learn something.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  12. Raulie New Member

    Nah, I was just trying to add some humor here. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]
  13. satya Moderator

    As far as my exp. in (various) forums concerned, most of the people who specify problem as URGENT doesn't seems to be a critical. <br /><br />You're right Lazy (like Bambola said great name to spell) 2 questions posted by Bzeebee contents the same URGENT word [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>
  14. derrickleggett New Member

    And I was soooooo very frightened. All for nothing. [<img src='/community/emoticons/emotion-6.gif' alt=':(' />]<br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  15. Luis Martin Moderator

    When life gives you a lemon, fire the DBA[<img src='/community/emoticons/emotion-2.gif' alt=':D' />].<br /><br />Luis Martin<br />Moderator<br />SQL-Server-Performance.com<br /><br /><font size="1">All postings are provided “AS IS” with no warranties for accuracy.</font id="size1"><br /><br />
  16. Raulie New Member

    Thanks! LAZY_DBA was chosen with a bit of irony in mind. It is like calling a 7 foot 400 pound guy tiny. In my profile I included the philosophy behind the meaning of LAZY_DBA.

Share This Page