Updating computed columns | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Updating computed columns

Hi Guys,<br /><br />Can anybody tell me how I can update computed columns (preferably on a system table)<br /><br /><br />Starwars8818<br /><i></i>Think, Think, Think again. Blessed you are for being born a human. [<img src=’/community/emoticons/emotion-3.gif’ alt=’:eek:‘ />)]
Why it is required on a system table?
In general its not recommended to update system tables directly. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
(preferably on a system table)<br /><br />DON’T!!!! <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ /> Think, Think, Think again. Blessed you are for thinking before do. Where’s the little green guy icon?<br /><br />Otherwise, you would use ALTER TABLE.<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
Hi guys, Lemme put it straight forward. I wanted to update sysstats in sysobjects to hide the objects in some user database. converting (masking) the second bit from right in the sysstats column of sysobjects should hide the object. Ofcourse you can hide system objects from EM, but I want to hide user objects from T-SQL. Guys help me around………. Starwars8818
Think, Think, Think again. Blessed you are for being born a human.
The big question why to hide the objects, there is more harm than any use by performing update on system tables. And MS will not support if you have contract with them. I follow and refer Kalen Delaney’s reference:
The jury’s still out about whether directly accessing SQL Server system tables is a good thing. The SQL Server 7.0 documentation states explicitly that you shouldn’t access system tables, but the SQL Server 2000 documentation loosens up that advice a bit: Reference of documented columns in system tables is permissible. However, many of the columns in system tables are not documented. Applications should not be written to query or update undocumented columns directly. What about updating the system tables? The documentation stresses that "system tables should not be altered directly by any user." I completely agree with this advice. With older versions of SQL Server, I sometimes had to directly update a system table because they offered no other way to do what I needed. But in SQL Server 7.0 and 2000, Microsoft supplies enough system procedures and other tools to do any system-table modification I’ve required.
To highlight what Derrick mentioned, to update the system tables SP_CONFIGURE must be used and further information :
Execute permissions on sp_configure with no parameters, or with only the first parameter, default to all users. Execute permissions for sp_configure with both parameters, used to change a configuration option, default to the sysadmin and serveradmin fixed server roles. RECONFIGURE permissions default to the sysadmin fixed server role and serveradmin fixed server role, and are not transferable.
HTH
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
If you want this level of viewing rights, then you need to control it at the user level. Set up a user and specifically deny them access to anything they don’t have access to. Screwing around with sysobjects is not the way to go about doing this. Make sense? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
OK….If you insist on hiding your tables through this perilous route, here is what you have to do: 1. Enable modofication of system objects through the properties dialog box of the SQL server instance –General tab
2. Open the sysobjects table
3. Change the value in the xtype field for the table to "S" from "U"
4. Close the sysobjects table
5. Refresh the view This is a little bit safer coz’ you don’t need to touch the sysstat field at all. Nathan H.O.
Moderator
SQL-Server-Performance.com
Hi guys, In simple words, I don’t want to do this in production environment. Only in Test server which developers will also be using (NOT IMPORTANT SERVER). I want to keep some data regarding appraisals temporarily in that table before I move it on. The name may be tempting to some users to dig and find out what it contains. In the first place they should not be able to see the table at all. I want to avoid permissions regarding database and server roles. Please eliminate this option out totally. FORGET EVERYTHING !! JUST TELL ME ONE THING:- How do I update computed columns??
Ok, you’re clear now.
You may follow Nathan’s steps or here is what I did previously at my test environment. Open ISQL/w and USE the pubs database.
In the Query text box, type
SELECT * FROM sysobjects
WHERE type = ‘U’ Change the sysstat value of publishers from 83 to 81. (The sysstat value of 83 represents the bit setting 1010011. The second bit from the right is the display bit, which you must turn off: 1010001. This bit setting corresponds to sysstat value 81.)
Allow updates to the system tables by entering the following statements in ISQL/w:
sp_configure ‘allow updates’, 1
RECONFIGURE with override
go Update sysobjects by entering the following statements in ISQL/w:
SET sysstat= (sysstat^2)
WHERE name = ‘publishers’
sp_configure ‘allow updates’, 0
RECONFIGURE with override
go This command tells the system to flip the current setting of the second bit in sysstat. The first time you run this command, it turns off the bit; rerunning the command turns on the bit. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Hi satya. On running SET sysstat = (sysstat^2)
WHERE name = ‘publishers’
sp_configure ‘allow updates’, 0
RECONFIGURE with override
go
I get Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘=’.
I also added UPDATE SYSOBJECTS to the first line. Error given is Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near ‘sp_configure’.
Starwars8818
As you’re over the first error by adding UPdate sysobjects, just add GO before SP_CONFIGURE in order to complete that statement.
(execute both statements seperately) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
]]>