SQL Server Performance Forum – Threads Archive

Updating sys tables in SQL2005

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

You don’t.

Why? —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

Hi Adriaan, Can i know why can’t we update a system table in sql2005.This facility is present in SQL2000. Cheers,
Manoj

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.

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. Cheers,
Manoj

You didnt replay to Frank’s question Why do you want to update system table? Madhivanan Failing to plan is Planning to fail

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. Cheers,
Manoj

Actually my question wasn’t incomplete. [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Modifications to system tables finally aren’t possible in SQL Server 2005 anymore. You have to find another method. – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

the requirement should be that your data is secure
the requirement is not your verification methodology

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
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.

Satya, 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. Cheers
Manoj

You clearly have not read the replies that you already have.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Adriaan You clearly have not read the replies that you already have. <hr height="1" noshade id="quote"></blockquote id="quote"> http://www.bofh.com/store/cluebyfour.html</a> [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)] Just kidding around folks! manojsm_9, you cannot update sys tables anymore. You will have to find another way to accomplish what you want. 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…

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
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

]]>