SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Object Owner Changes to Database System Objects


Object Owner Changes to Database System Objects

Author
Message
carnaud
carnaud
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118067 Visits: 45534
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

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


carnaud
carnaud
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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


............................
.........
.
.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118067 Visits: 45534
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

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


carnaud
carnaud
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 62
LOL!

Problem solved (I believe). I was one of our own DBA's [name withheld].
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118067 Visits: 45534
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

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


carnaud
carnaud
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118067 Visits: 45534
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

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


carnaud
carnaud
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search