Accessing more than: enabled

Showing posts with label enabled. Show all posts

Sunday, February 12, 2012

Ad hoc updates to system catalogs are not enabled

Hi,
Some time ago, following a security recommendation, I deleted
sp_change_users_login. Now I want it back. I scripted it as create
from another server. When I try to run it in QA as sa I get "Ad hoc
updates to system catalogs are not enabled The system administrator
must reconfigure SQL Server to allow this.
Server: Msg 259, Level 16, State 1, Procedure sp_change_users_login,
Line 197
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this.
Thanks,
PeterHi Peter,
My name is Michael and I would like to thank you for using Microsoft
newsgroup.
Please try to perform the following SQL statements before you run the
statements for adding the stored procedure.
SP_CONFIGURE 'ALLOW UPDATES', 1
RECONFIGURE WITH OVERRIDE
After adding the stored procedure, please perform the following SQL
statements for safe reason.
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE
allow updates Option
Use the allow updates option to specify whether direct updates can be made
to system tables. By default, allow updates is disabled (set to 0), so
users cannot update system tables through ad hoc updates. Users can update
system tables using system stored procedures only. When allow updates is
disabled, updates are not allowed, even if you have the appropriate
permissions (assigned using the GRANT statement).
When allow updates is enabled (set to 1), any user who has appropriate
permissions can update system tables directly with ad hoc updates and can
create stored procedures that update system tables.
For more information regarding SP_CONFIGURE, please refer to the following
article on SQL Server Books Online.
Topic: "SP_CONFIGURE"
Topic: "allow updates Option"
Thanks for choosing Microsoft.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Peter,
Thank you for choosing Microsoft! Michael is on holiday and I'm his backup.
My name is
Billy and it's my pleasure to further assist you with this issue.
I believe Machael has pointed out the root cause of your issue and his solut
ion is accurate
and workable on your side. For your benefits, here I'd like to follow up wit
h something
important you should pay more attention to, as the system catalogs are very
critical to the
operation of SQL Server.
Please keep in mind that updating fields in system tables can prevent an ins
tance of SQL
Server from running or can cause data loss. If you create stored procedures
while the allow
updates option is enabled, those stored procedures always have the ability t
o update
system tables even after you disable allow updates. On production systems, y
ou should not
enable allow updates except under the direction of Microsoft Product Support
Services.
It is stongly recommend that you enable allow updates only in tightly contro
lled situations.
Prevent other users from accessing SQL Server while you are directly updatin
g system
tables by restarting an instance of SQL Server from the command prompt with
sqlservr -m.
This command starts an instance of SQL Server in single-user mode and enable
s allow
updates.
After successfully updating the system catalogs, please remember changing th
e allow
updates back to 0 AT ONCE, and then restart the instance services.
For more information on how to operate it in minimal configuration mode, ple
ase see the
following topic in Books Onlinie:
"Starting SQL Server with Minimal Configuration"
Thanks for choosing Microsoft.
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

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 schema views: TABLES) are
views.
Why would you want to hack the system tables? If you have a real case for this, 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 not 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...
>> 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?
>|||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 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...
>> 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?
>>
>|||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...
>> 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...
>> 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?
>>
>>
>|||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 to
> 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:
>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 to
>> figure out another way to do what you want to do that doesn't involve
>> updating system tables.|||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...
> 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
>> to figure out another way to do what you want to do that doesn't involve
>> updating system tables.|||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...
> 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
>> to figure out another way to do what you want to do that doesn't involve
>> updating system tables.|||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'.
>
I executed the query to show that I could still get at sysobjects, the
fact that I queried for user tables was a "distraction". I should have
just left off the where clause.
However, I did not realize that the table had been replaced by a view.
As long as all of my old queries still work I am happy.
Thanks all.
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'.
>
I executed the query to show that I could still get at sysobjects, the
fact that I queried for user tables was a "distraction". I should have
just left off the where clause.
However, I did not realize that the table had been replaced by a view.
As long as all of my old queries still work I am happy.
Thanks all.
E|||> As long as all of my old queries still work I am happy.
There's a good section in Books Online about what differences there are between the old system
tables and the new compatibility views. Some documented stuff has been changed (mostly minor things)
and if you are using undocumented columns be prepared for some work. See in the Upgrading section
for what has changed in the documented part.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Eric McGrane" <EricMcGrane@.crd.com> wrote in message news:4464EFD3.5010900@.crd.com...
> 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'.
> I executed the query to show that I could still get at sysobjects, the fact that I queried for
> user tables was a "distraction". I should have just left off the where clause.
> However, I did not realize that the table had been replaced by a view.
> As long as all of my old queries still work I am happy.
> Thanks all.
> E

Sunday, February 12, 2012

Ad hoc updates to system catalogs are not enabled

Ad hoc udpates to system tables in 2005 GONE!

Ad hoc udpates to system tables in 2005 GONE!

Accessing more than

Blog Archive

About Me