SQL 2005 Table keeps being deleted

Question

SQL 2005 Table keeps being deleted

Mints

SSC Journeyman

Points: 98
More actions
April 2, 2012 at 4:30 am

#271264

Hi Guys,
Sorry for the basic question, I have an issue in SQL 2005 where a table keeps going missine, I'm sure it's a process that's running that is causing it to be deleted but not sure how to identify what it is. Would any of you be able to point me in the right direction ?
Thanks in advance.:cool:

Viewing 6 posts - 1 through 5 (of 5 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Open up the default trace and look for Drop Object events. That will give you time, login name, host name and a lot of other info. (or if you don't feel like messing with code open up the Schema Change report which is based off the default trace)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 2

Is this maybe a temp table created for and by any procedures?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Mints SSC Journeyman Points: 98 More actions · Answer 3

Thanks for the reply guys,

GilaMonster - I think you gave me the perfect direction to investigate in, I can see all the information I need to identify the user/process that is deleting the table. Thanks very much

Henrico - It wasn't a temp table, but thanks for replying.

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 4

There is also a report that you can run on the database, open ssms, navigate to your database, RMC and selct reports, General reports, schema changes.

***The first step is always the hardest *******

Eric M Russell SSC Guru Points: 125563 More actions · Answer 5

Pete.murray (4/2/2012)
Thanks for the reply guys,
GilaMonster - I think you gave me the perfect direction to investigate in, I can see all the information I need to identify the user/process that is deleting the table. Thanks very much
Henrico - It wasn't a temp table, but thanks for replying.

Once you've identified the account that keeps dropping the table, here is a suggestion for what to do next:

EXEC master..sp_dropsrvrolemember @loginame = '<account>', @rolename = 'sysadmin';

EXEC sp_droprolemember 'db_owner', '<account>';

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho