Object Owner Changes to Database System Objects

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Security » Object Owner Changes to Database System...

Object Owner Changes to Database System Objects

Rate Topic

Display Mode

Topic Options

Author

Message

carnaud

Posted Wednesday, June 30, 2010 3:54 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62

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

Post #945826

GilaMonster

Posted Wednesday, June 30, 2010 11:40 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 38,068, Visits: 30,361

Check the error logs, see if allow updates has been enabled recently.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #945930

carnaud

Posted Thursday, July 01, 2010 6:34 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62

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

............................
.........
.
.

Post #946128

GilaMonster

Posted Thursday, July 01, 2010 6:42 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 38,068, Visits: 30,361

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 2008, MVP
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

Post #946131

carnaud

Posted Thursday, July 01, 2010 8:47 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62

LOL!

Problem solved (I believe). I was one of our own DBA's [name withheld].

Post #946224

GilaMonster

Posted Thursday, July 01, 2010 9:00 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 38,068, Visits: 30,361

Did he admit to updating the system objects? Did he give any reason why he did that?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #946235

carnaud

Posted Thursday, July 01, 2010 11:05 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62

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.

Post #946344

GilaMonster

Posted Thursday, July 01, 2010 11:10 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 38,068, Visits: 30,361

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 2008, MVP
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

Post #946350

carnaud

Posted Thursday, July 01, 2010 11:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 01, 2010 10:16 AM
Points: 29, Visits: 62

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!

Post #946354

« Prev Topic | Next Topic »

Permissions