SQL Server Performance Forum – Threads Archive

Auditing – Unexplained Mysteries

I have noticed some strange occurences in my databases. Whenever I delete a table from my production server, I rename it with PDEL_ and then I wait. After a couple weeks when it is truly not needed any more, I script the table out and I delete it. A table I had marked for deletion appeared back in the database as though it had been restored. It’s twin, the original I had marked for deletion by renaming, was still there with PDEL_ before it. We have a web application that uses a DTS package to pull information from an excel spreadsheet and import it into a table in our database. This procedure failed after we changed our SA password. This DTS package was updated at the beginning of the year to not use the SA account so it was a surprise when it failed because the DTS package was still using the SA account. We have a trigger that reports off a specific ID. This trigger has been working just fine for months. On Friday it stopped working because it had an ID that belonged to a different database with the same schema. Most of this looks almost like old versions are being restored though I can not be sure. Sadly I’ve been trying to lock down this database server for awhile now but even as the database administrator here, it is hard to enforce certain policies in a company this small. Never the less, I would like to find out who or what is doing this as no one likes their work undone or someone messing around where they don’t belong. I am set up to be emailed every time there is a failure to log in and I have noticed some strange user names I don’t recognize such as ‘Admin’. I’m trying to find articles on what I need to run in Profiler to track where these are coming from. I’ve also been told that I should set up trace flags for auditing but I’m very very naive and new to trace flags. Can any one give me some advice?

Are you talking about auditing logins to the actual windows server or the sql server? Do people have physical access to the box or via terminal services or do they have enterprise manager access only? To log users of the sql server and where they come from you could use a script like this one: http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=298</a> Then you can find out from which workstations people are logging in to the sql server. If possible, change the sa password again so that only you know it. Also check for other sql server accounts that have the sysadmin role. Change password on them too if possible or restrict the rights on them. Then lock down the sql server further by removing the BUILTINAdministrators account under "Security -> Logins" that allow any Windows NT administrator to access the sql server. On how to do that see: http://support.microsoft.com/default.aspx?scid=kb;en-us;263712</a> This assumes nobody uses these accounts. If they do you someone will scream eventually <img src=’/community/emoticons/emotion-4.gif’ alt=’

‘ />. Might be better to start auditing for a while with the script above. Problem is if people log on locally on the server as a windows administrator you won’t see who it was. But you could identify if someone did login at other times than you did by modifying the script to log each single login and not only the first and last access date.

I would also set up a couple Profilers immediately. Set up one to monitor login failures and be sure to include HostName, ApplicationName, and ClientProcessID. Set up one to monitor all ddl statements such as CREATE, ALTER. The Admin sounds like an Access database. A lot of times, this is the username they use to login. If they are using a DSNless connection with Trusted Security and have not added ;Trusted Connection = Yes or whatever it is in the connection string, any failed logins will pass Admin to the Profiler. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

Thanks! I will let you know how it turns out. This is a great place to start.

]]>