SQL Server Performance

Updating sys tables in SQL2005

Discussion in 'SQL Server 2005 General DBA Questions' started by manojsm_9, Oct 23, 2006.

  1. manojsm_9 New Member

    Hi Guys,

    Since i am new to SQL 2005 does any one have the idea of updating the system table in sql2005 ????

  2. Adriaan New Member

    You don't.
  3. FrankKalis Moderator

  4. manojsm_9 New Member

    Hi Adriaan,

    Can i know why can't we update a system table in sql2005.This facility is present in SQL2000.

  5. Adriaan New Member

    Any action query you might be able to run "against system tables" presents the risk of rendering the database and/or server inoperable.

    Whatever you're trying to do, there is a system stored procedure that you must use, instead of manipulating data in system tables.

    I often run select queries against system tables, instead of using the impractical INFORMATION_SCHEMA views, but I never ever would dare to run action queries against system tables.
  6. Madhivanan Moderator

    quote:Originally posted by manojsm_9

    Hi Adriaan,

    Can i know why can't we update a system table in sql2005.This facility is present in SQL2000.

    You didnt replay to Frank's question

    Why do you want to update system table?


    Failing to plan is Planning to fail
  7. manojsm_9 New Member

    Hi Madhivanan,

    Franks Question was incomplete so my reply....

    I have created a database in SQL2005 which has very confidential information.Now within the database i have created a table test1 and used the Encryption logic at Column level.Now since a particular key (key value is stored in system table)is assigned to the a particular table for the encryption;i want to test by updating value as this is a requirement or the PLAN given to us.

  8. FrankKalis Moderator

    Actually my question wasn't incomplete. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />Modifications to system tables finally aren't possible in SQL Server 2005 anymore. You have to find another method.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  9. joechang New Member

    the requirement should be that your data is secure
    the requirement is not your verification methodology
  10. satya Moderator

    So for that reason DMVs are introduced where you can discourage using system tables reference directly in queries. I would suggest to choose the SYMMETRIC key method in SQL 2005 to modify the required value.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
  11. manojsm_9 New Member


    I am using simple symmetric key method in sql2005.In this also there is a reference to the system table.
    i.e SELECT * FROM sys.symmetric_keys.

    When i am trying to update the system table(sys.symmetric_keys) it is not giving me to modify or update the table.

  12. Adriaan New Member

    You clearly have not read the replies that you already have.
  13. Haywood New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />You clearly have not read the replies that you already have.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br /><br /<a target="_blank" href=http://www.bofh.com/store/cluebyfour.html>http://www.bofh.com/store/cluebyfour.html</a> [<img src='/community/emoticons/emotion-3.gif' alt=':eek:' />)]<br /><br /><br />Just kidding around folks! manojsm_9, you cannot update sys tables anymore. You will have to find another way to accomplish what you want.<br /><br />Common security knowledge/practice also dictates that having the ability to update a 'key manager' insecurely invalidates almost all of the security put into place; which sounds a bit like what you're trying to do...
  14. FrankKalis Moderator

    Yes, and to be clear about updating the system tables. Even in SQL Server 2000 this is a very bad idea. You won't get any support by MS, when they find out you did update that tables. You're on your own in any case of disaster. Not nice expectations for the guy in charge of responsibility.

    Frank Kalis
    Microsoft SQL Server MVP

Share This Page