June 30, 2010 at 3:54 pm
I have been working with MS SQL Server going back 14 years. I have NEVER seen this occur.
"Someone" or "Something" recently commit a mass update on a SQL 2000 server instance that changed the owner of ALL system objects in a number of the databases to sa FROM dbo.
i.e: select * from dbo.sysobjects
resulted in:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.sysobjects'.
However...upon further inspection
select * from sa.sysobjects
Worked....
It was a simple matter to revert the ownership, although one of the databases was corrupted for reasons unknown.
Does anyone have an idea how this could have possibly occurred or fathom a reason why someone (perhaps a hacker) would do something so stupid? It is possible that it was a RAID failure, corrupted memory, sun spots, whatever...but I would tend to think that this was deliberate (or accidental) as the databases concerned did not contain something as simple as a list of someone's list favorite foods. Is pretty confidential data.
Thanks,
Clay
June 30, 2010 at 11:40 pm
Check the error logs, see if allow updates has been enabled recently.
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
July 1, 2010 at 6:35 am
Yes. In fact it was. Looks like the server was recovering from a hard reboot the two days previous however, the databases involved were working fine for almost two days before this "event occured".
Here is a log extract (some items are censored):
2010-06-28 06:00:02.03 spid88 Server Process ID is [xxx]
2010-06-28 06:00:02.03 spid88 Logging SQL Server messages in file '[xxxxxxxxxxxxxxxxxxxx]'.
2010-06-28 06:00:02.03 spid88 Errorlog has been reinitialized. See previous log for older entries.
2010-06-28 12:59:30.55 spid107 Starting up database '[xxxxxxxxx]'.
2010-06-28 12:59:30.56 spid107 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 12:59:31.02 spid107 Starting up database '[xxxxxxxxx]'.
2010-06-28 12:59:31.02 spid107 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 12:59:31.16 spid107 Recovery is checkpointing database '[xxxxxxxxx]' (26)
2010-06-28 12:59:31.45 spid107 Starting up database '[xxxxxxxxx]'.
2010-06-28 12:59:31.45 spid107 Analysis of database '[xxxxxxxxx]'. (26) is 100% complete (approximately 0 more seconds)
2010-06-28 12:59:31.66 backup Database restored: Database: '[xxxxxxxxx]', creation date(time): 2006/04/04(17:20:02), first LSN: 140408:3076:594, last LSN: 140408:3156:1, number of dump devices: 7, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_xxxxxx-D690-4BF9-FDFF-BC49E5810AA7_0', 'VDI_1DF31FC3-D690-4BF9-xxx-BC49E5810AA7_1', 'VDI_1DF31FC3-xxx-4BF9-8BAD-BC49E5810AA7_2', ...}).
2010-06-28 13:00:44.49 spid69 Starting up database '[xxxxxxxxx]' .
2010-06-28 13:00:44.49 spid69 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 13:00:44.86 spid69 Starting up database '[xxxxxxxxx]' .
2010-06-28 13:00:44.86 spid69 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 13:00:45.00 spid69 Recovery is checkpointing database '[xxxxxxxxx]' (27)
2010-06-28 13:00:45.33 spid69 Starting up database '[xxxxxxxxx]' .
2010-06-28 13:00:45.47 backup Database restored: Database: '[xxxxxxxxx]' , creation date(time): 2008/12/01(11:31:02), first LSN: 35:20794:1, last LSN: 35:20796:1, number of dump devices: 7, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_17AD1DA6-YYYY-46FE-B55A-DBCF525FB117_0', 'VDI_17AD1DA6-xxx-46FE-B55A-DDSSS25FB117_1', 'VDI_17AD1DA6-2BE9-46FE-B55A-DBCF525FB117_2', ...}).
2010-06-28 13:43:38.11 spid91 Starting up database '[xxxxxxxxx]' .
2010-06-28 13:43:38.13 spid91 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 13:43:38.55 backup Database restored: Database: '[xxxxxxxxx]' , creation date(time): 2006/03/22(11:34:25), first LSN: 498:9680:1, last LSN: 498:9682:1, number of dump devices: 7, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_1568B45C-xxx-4DE2-FFDD-51D292BF53F0_0', 'VDI_1568B45C-xxx-4DE2-903A-51D292BF53F0_1', 'VDI_1568B45C-5841-4DE2-903A-51D292BF53F0_2', ...}).
2010-06-28 13:43:41.69 spid91 Starting up database '[xxxxxxxxx]'.
2010-06-28 13:43:41.71 spid91 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 13:43:42.21 backup Database changes restored: Database: '[xxxxxxxxx]' , creation date(time): 2006/03/22(11:34:25), first LSN: 498:9753:1, last LSN: 498:9755:1, number of dump devices: 7, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_311BD8EE-74DF-AAA8-AFDE-558FA547D459_0', 'VDI_311BD8EE-74DF-4E28-AFDE-558FA547D459_1', 'VDI_311BD8EE-74DF-xxx-AFDE-558FA547D459_2', ...}).
2010-06-28 13:43:43.15 spid90 Starting up database '[xxxxxxxxx]' .
2010-06-28 13:43:43.15 spid90 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 13:43:43.26 spid90 Recovery is checkpointing database '[xxxxxxxxx]' (7)
2010-06-28 13:43:43.55 spid90 Starting up database '[xxxxxxxxx]' .
2010-06-28 13:43:43.88 spid90 Error: 15457, Severity: 0, State: 1
2010-06-28 13:43:43.88 spid90 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-06-28 13:43:43.90 spid90 Error: 15457, Severity: 0, State: 1
2010-06-28 13:43:43.90 spid90 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-06-28 13:43:44.24 spid90 Process ID 51 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 52 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 54 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 55 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 57 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 67 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 68 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 82 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 87 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 94 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 102 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 106 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 13:43:44.24 spid90 Process ID 131 killed by hostname [Server Name], host process ID [xxx].
2010-06-28 15:12:54.88 spid51 Starting up database '[xxxxxxxxx]' .
2010-06-28 15:12:54.89 spid51 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 15:13:11.28 backup Database restored: Database: '[xxxxxxxxx]' , creation date(time): 2005/12/03(18:55:27), first LSN: 1333201:20733:1, last LSN: 1333226:3860:1, number of dump devices: 7, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'VDI_DF7A9D6E-27C6-4619-9BFB-5DC8B07AAEB5_0', 'VDI_DF7A9D6E-27C6-4619-9BFB-5DC8B07AAEB5_1', 'VDI_DF7A9D6E-27C6-4619-9BFB-5DC8B07AAEB5_2', ...}).
2010-06-28 15:13:12.02 spid90 Starting up database '[xxxxxxxxx]' .
2010-06-28 15:13:12.02 spid90 Bypassing recovery for database '[xxxxxxxxx]' because it is marked IN LOAD.
2010-06-28 15:13:18.93 spid90 Recovery is checkpointing database '[xxxxxxxxx]' (93)
2010-06-28 15:13:19.25 spid90 Starting up database '[xxxxxxxxx]' .
2010-06-28 15:13:22.52 spid90 Analysis of database '[xxxxxxxxx]' (93) is 100% complete (approximately 0 more seconds)
2010-06-28 15:13:23.83 spid90 Error: 15457, Severity: 0, State: 1
............................
.........
.
.
July 1, 2010 at 6:42 am
Don't suppose you keep a c2 trace or anything similar? Would be interesting to know who SPID 90 was.
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
July 1, 2010 at 8:47 am
LOL!
Problem solved (I believe). I was one of our own DBA's [name withheld].
July 1, 2010 at 9:00 am
Did he admit to updating the system objects? Did he give any reason why he did that?
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
July 1, 2010 at 11:05 am
It was done in the context of legitimate administrative activates. Unfortunately, it had a negative impact due to state of the environment at the time. There were a number of transactional data loads going on at the time and I expect the server just cr@pped it’s pants due to the DBA’s activities at the time.
Chalk it up to lessons learned
Thanks for the suggestion concerning looking into allow updates! Good stuff.
July 1, 2010 at 11:10 am
Direct updates should NEVER be done to the system tables. It's all too easy to break lots of things and not realise until far too late.
When you have a little maintenance window, please run DBCC CheckCatalog on that database (CheckDB does not run CheckCatalog on sQL 2000). Make sure you don't have further lingering issues.
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
July 1, 2010 at 11:13 am
I realize that. DBCCs have been done and everything is back as it should be.
Thanks again for helping out. I really appreciate your input!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply