can we modify system tables in SQL Server 2000?

Question

can we modify system tables in SQL Server 2000?

Viewing 14 posts - 16 through 28 (of 28 total)

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

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

Sergiy (9/28/2007)
Hope you did not miss this statements:
"Updating fields in system tables can prevent an instance of Microsoft® SQL Server™ from running or can cause data loss. "
"On production systems, you should not enable allow updates except under the direction of Microsoft Product Support Services."
I gave you that answer straight away.
It's nobody's fault you did not get it.

What if I just want to delete orphaned users in sysusers and i enabled the "allow updates" then disabled it after deleting the orphaned user?Am I doing a very risky thing? what possible disaster may occur?

"-=Still Learning=-"

Lester Policarpio

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

Lester Policarpio (10/1/2007)[hr
What if I just want to delete orphaned users in sysusers and i enabled the "allow updates" then disabled it after deleting the orphaned user?Am I doing a very risky thing? what possible disaster may occur?

You're doing an extremely risky thing. That's exactly what my collegues were doing that lead to my having to fix unowned tables.

That said, any direct modification of the system tables is risking problems.

May I suggest sp_revokedbaccess if the user needs to be removed, or sp_change_users_login if the user needs to be mapped to a different login?

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

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 3

Thanks for the info guys 😀

"-=Still Learning=-"

Lester Policarpio

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

What if I just want to delete orphaned users in sysusers and i enabled the "allow updates" then disabled it after deleting the orphaned user?Am I doing a very risky thing? what possible disaster may occur?

In 2004, we thought the same thing... involved an IS NULL... went to update 1500 customers WHERE somecolumnname IS NULL... code had been tested in "cloned server" and it worked just fine... SQL Server had a fault in it... if a certain type of parallelism formed with the right # of CPU's along with just the right indexes, the WHERE somecolumnname IS NULL was ignored. DBA's hadn't checked backup viability in weeks and, of course, they had been failing all over the place. The IS NULL in the update was ignored because of the MS fault and 800 THOUSAND customers were updated... no one knew until we tried to invoice them. Took 40 people 3 days to rebuild the data from pieces of other databases.

What possible disaster may occur? Something could go wrong... something like the above, or maybe you get in a hurry and forget some criteria and also didn't do a BEGIN TRANSACTION at the start of your change. Maybe you update a column that has one of those lovely "undocumented features" associated with it that'll train-wreck your DB. Dunno... I just wouldn't take the chance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Ninja's_RGR'us

SSC Guru

Points: 294069

October 1, 2007 at 9:02 pm

#737977

Halloween bug?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

Heh... maybe... it did turn our shining carriage into a pumkin...:D

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

We should have a best comeback constest... this one would start as a favorite :P.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Nah... we'd loose the audiance between comebacks just like we did on this one 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I'm still part of the audiance, and I think we should get another recall :).

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

Heh... I'm old... I've got no "recall" left until I've had about 20 coffees (if I recall correctly) 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Now the magic is gone... We should have stopped while we were ahead! 😉

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 12

GilaMonster (10/1/2007)
Lester Policarpio (10/1/2007)[hr
What if I just want to delete orphaned users in sysusers and i enabled the "allow updates" then disabled it after deleting the orphaned user?Am I doing a very risky thing? what possible disaster may occur?
You're doing an extremely risky thing. That's exactly what my collegues were doing that lead to my having to fix unowned tables.
That said, any direct modification of the system tables is risking problems.
May I suggest sp_revokedbaccess if the user needs to be removed, or sp_change_users_login if the user needs to be mapped to a different login?

Thanks thats exactly what I need 😀

"-=Still Learning=-"

Lester Policarpio

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

Ninja's_RGR'us

SSC Guru

Points: 294069

October 3, 2007 at 5:11 am

#738544

Your Dev want to touch the system tables too?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 14

Heh... already are... hardcore DBA just wouldn't allow this.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)