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
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='' />eleted. Adjust the columns and filters you want. You can zero in the perpatrator this way. Good Luck.
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.
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.
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.
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.
"?? 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.
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.
Yes, that's what I mean. Luis Martin Moderator SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
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.
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.
Nah, I was just trying to add some humor here. [<img src='/community/emoticons/emotion-2.gif' alt='' />]
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>
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='' />]<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
When life gives you a lemon, fire the DBA[<img src='/community/emoticons/emotion-2.gif' alt='' />].<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 />
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.
