Ad hoc udpates to system tables in 2005 GONE!

Is it true? I enabled this and when I ran RECONFIGURE it said this was not
supported. Is there another option I have to set first?Yes, it is true. In fact, we cannot even see the real system tables anymore.
All we can see
(compatible views: sysobjects, catalog views: sys.objects and information sc
hema views: TABLES) are
views.
Why would you want to hack the system tables? If you have a real case for th
is, let MS know and they
should provide you with a supported interface to accomplish what you need:
http://lab.msdn.microsoft.com/productfeedback/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uDOfDuQdGHA.3712@.TK2MSFTNGP03.phx.gbl...
> Is it true? I enabled this and when I ran RECONFIGURE it said this was no
t supported. Is there
> another option I have to set first?
>|||Hi Tim
You can't even SEE system tables in SQL 2005, much less change them.
What do you want to do that you think you need this option for? You need to
figure out another way to do what you want to do that doesn't involve
updating system tables.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:uDOfDuQdGHA.3712@.TK2MSFTNGP03.phx.gbl...
> Is it true? I enabled this and when I ran RECONFIGURE it said this was
> not supported. Is there another option I have to set first?
>|||We have a backup from one server that was restored on a different server.
The uid that owns some objects in that db don't match the uid as defined on
that server...i.e.
Server 1
uid=1 name=web
Server 2
uid=2 name=web
Can't make permissions work right unless these uid's sync up. In the past,
I'd just momentarily enable ad-hoc updates and update the the uid in the
database sysusers table. This always fixed the issue before.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:O64pv8QdGHA.4264@.TK2MSFTNGP05.phx.gbl...
> Hi Tim
> You can't even SEE system tables in SQL 2005, much less change them.
> What do you want to do that you think you need this option for? You need
> to figure out another way to do what you want to do that doesn't involve
> updating system tables.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:uDOfDuQdGHA.3712@.TK2MSFTNGP03.phx.gbl...
>|||This is what sp_change_users_login is for...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:eNiSFITdGHA.1856@.TK2MSFTNGP03.phx.gbl...
> We have a backup from one server that was restored on a different server.
The uid that owns some
> objects in that db don't match the uid as defined on that server...i.e.
> Server 1
> uid=1 name=web
> Server 2
> uid=2 name=web
>
> Can't make permissions work right unless these uid's sync up. In the past
, I'd just momentarily
> enable ad-hoc updates and update the the uid in the database sysusers tabl
e. This always fixed
> the issue before.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:O64pv8QdGHA.4264@.TK2MSFTNGP05.phx.gbl...
>|||Thanks...that is just what I needed. I probably just need to take an
evening and go through BOL entries for all those system procs.....I just
never seem to "have the time" yet we can make time to continually do it the
hard way....
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uAI$qrTdGHA.5036@.TK2MSFTNGP05.phx.gbl...
> This is what sp_change_users_login is for...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:eNiSFITdGHA.1856@.TK2MSFTNGP03.phx.gbl...
>|||Kalen,
What do you mean by "can't see" them? They still seem very accessible
to me:
select top 20 name from sysobjects where type='U'
name
--
TS_ORDER_BROKER
CS_TEST_GROUP_PRIVILEGE
FIX_TRADER
CSM_STATE
TS_ORDER_BROKER_TICKET
IMP_FEED
CSM_STATE_LIST
TS_ORDER_CODE
CSM_STATE_LIST_MEMBER
RTAPI_COLUMN
TS_ORDER_CONFIG
CSM_TIME_ZONES
CS_VIEW
TS_ORDER_FUND
IMP_FEED_COLUMN
TS_ORDER_GEN
CSM_TRANSACTION
IMP_FEED_TABLE
CSM_TRANSACTION_ORDER
CS_VIEW_TYPE_COLUMN
Regards,
E
Kalen Delaney wrote:
> Hi Tim
> You can't even SEE system tables in SQL 2005, much less change them.
> What do you want to do that you think you need this option for? You need t
o
> figure out another way to do what you want to do that doesn't involve
> updating system tables.
>|||A query from sysobjects where type = 'U' will return user
tables. You are looking at user tables. This thread is
discussing system tables.
-Sue
On Fri, 12 May 2006 09:36:10 -0400, Eric McGrane
<EricMcGrane@.crd.com> wrote:
[vbcol=seagreen]
>Kalen,
>What do you mean by "can't see" them? They still seem very accessible
>to me:
>select top 20 name from sysobjects where type='U'
> name
> --
> TS_ORDER_BROKER
> CS_TEST_GROUP_PRIVILEGE
> FIX_TRADER
> CSM_STATE
> TS_ORDER_BROKER_TICKET
> IMP_FEED
> CSM_STATE_LIST
> TS_ORDER_CODE
> CSM_STATE_LIST_MEMBER
> RTAPI_COLUMN
> TS_ORDER_CONFIG
> CSM_TIME_ZONES
> CS_VIEW
> TS_ORDER_FUND
> IMP_FEED_COLUMN
> TS_ORDER_GEN
> CSM_TRANSACTION
> IMP_FEED_TABLE
> CSM_TRANSACTION_ORDER
> CS_VIEW_TYPE_COLUMN
>Regards,
>E
>
>Kalen Delaney wrote:|||sysobjects is now a system view. it's no longer a _table_.
select objectproperty(object_id('sysobjects'),'
IsTable')
-oj
"Eric McGrane" <EricMcGrane@.crd.com> wrote in message
news:uX7rIkcdGHA.4576@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Kalen,
> What do you mean by "can't see" them? They still seem very accessible to
> me:
> select top 20 name from sysobjects where type='U'
> name
> --
> TS_ORDER_BROKER
> CS_TEST_GROUP_PRIVILEGE
> FIX_TRADER
> CSM_STATE
> TS_ORDER_BROKER_TICKET
> IMP_FEED
> CSM_STATE_LIST
> TS_ORDER_CODE
> CSM_STATE_LIST_MEMBER
> RTAPI_COLUMN
> TS_ORDER_CONFIG
> CSM_TIME_ZONES
> CS_VIEW
> TS_ORDER_FUND
> IMP_FEED_COLUMN
> TS_ORDER_GEN
> CSM_TRANSACTION
> IMP_FEED_TABLE
> CSM_TRANSACTION_ORDER
> CS_VIEW_TYPE_COLUMN
> Regards,
> E
>
> Kalen Delaney wrote:|||Ok, you caught me...
And Sue and oj caught you...
You are querying a view, as oj pointed out, not a system table. However, you
could change your query to look for System tables
select top 20 name from sysobjects where type='S'
and you will see some of the new system table names, which are very strange
and unlike anything we had in SQL Server 2000.(As oj mentions, sysobjects,
sysindexes, et al, are now VIEWS.)
But, what I actually meant was that you cannot see any of the data in the
system tables, at least not without jumping through hoops.
For example, one of my favorite system tables, whose name you will see when
you run the query above, is syshobts. So although you can see its name in
the sysobjects view, when you try to query it, with or without the schema
name, SELECT * from syshobts, you will get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.syshobts'.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Eric McGrane" <EricMcGrane@.crd.com> wrote in message
news:uX7rIkcdGHA.4576@.TK2MSFTNGP05.phx.gbl...[vbcol=seagreen]
> Kalen,
> What do you mean by "can't see" them? They still seem very accessible to
> me:
> select top 20 name from sysobjects where type='U'
> name
> --
> TS_ORDER_BROKER
> CS_TEST_GROUP_PRIVILEGE
> FIX_TRADER
> CSM_STATE
> TS_ORDER_BROKER_TICKET
> IMP_FEED
> CSM_STATE_LIST
> TS_ORDER_CODE
> CSM_STATE_LIST_MEMBER
> RTAPI_COLUMN
> TS_ORDER_CONFIG
> CSM_TIME_ZONES
> CS_VIEW
> TS_ORDER_FUND
> IMP_FEED_COLUMN
> TS_ORDER_GEN
> CSM_TRANSACTION
> IMP_FEED_TABLE
> CSM_TRANSACTION_ORDER
> CS_VIEW_TYPE_COLUMN
> Regards,
> E
>
> Kalen Delaney wrote: