MSSQL 2000 VS MSSQL 2005
Recently I have research some of the difference between MSSQL 2005 en 2000. I can't say that my firsts thoughts where that good but some further thoughts where quite impressive. I begin my journey to mark a user database suspect. This always could have been done by setting the status field to 320, thanks to the good old Sybase. But I know now that MSSQL 2005 works a little bit different.

Everything comes down to zero's and ones
In my highschool years I aqtually never saw the benefits for software developers to read binary en heximal data. For those who thought the same here's a little reference carte which really is necessary to read some of the MSSQL features.
 

From owner to schema
With MSSQL 2005 comes a little step to Oracle. The syntax <database_name>.<database_owner>.<table_name> is changed to <database_name>.<schema_owner>.<table_name>. This could create great problems if this would be done without taking further archited action. Therefore MSSQL has a schema name that belongs to dbo. And with that, the most used <database_name>.dbo.<table_name> syntax is prepared for MSSQL 2005.

Behind the scene
The architected from MSSQL 2000 still was a copy from the old Sybase concept and allthough the claime it is been rewritten the diagram is 90 percent identical. With the arrival of MSSQL 2005 a new wind has blown. As an example the sysdatabases table is exploit in this articel. At first, if you have an MSSQL 2005 instance running, try to select a system table in MSSQL 2005, such as:

use [master]
go
select * from dbo.sysdatabases

with that, your first reaction could be (as mine) "Nothing has changed". Well not quite, because all system tables are moved to the sys-schema and dbo.sysdatabases is nothing more than a view (proved by: sp_help sysdatabases). sp_helptext sysdatabases says that we have a view called 'sys.sysdatabases' so if that would be the complete metamorphose the changes would be real Microsofts, buy the product, make some little layout changes and give it your own name. Well there goes that assumption.
Because try a sp_helptext of 'sys.sysdatabases' and you can see that 'sys.sysdatabases' just is an preparation of something larger than a layout change. sys.sysdatabases reveres to a complete new object "sys.databases". by selecting the content of it you can see that a great deal of the changes where nessesary for enabling the snapshot cappabilities. You can also see why you can never see the mssqlsystemresource database according to "WHERE d.id < 0x7fff ". Yes that's right, mssqlsystemresource has an id thats one decimal lager than 0x7fff. And don't try to update the id one lower because the MSSQL engine refers straight to this number and not the name.
Let's go back to the suspect marking of a database, just edit the sys.databases set the status field to 320 right? Wrong. "To confuse the russians" as an instructor ones said, this is another view... Yes, we have now three views and we are still trying to mark a user database suspect.
This view refers to "master.sys.sysdbreg", here comes the nasty part; try to select it in your Management Studio. Such as:

use [master]
go
select * from sys.sysdbreg

An error occure with a message that sounds like:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysdbreg'.

So while the view in the sys-schema can refers to it, it isn't possible to select it in the Managment Studio. There must be an backdoor right? Indeed there is. Follow the next steps:

• Stop your SQLSERVER with a normal TSQL command like: shutdown
• Start your instance in single user mode like: "C:\PROGRAM FILES\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe -m" in a normal MSDOS command box.
• Start the new sqlcmd feature with an dedicated admin connection, such as: sqlcmd -A -E
  For a full list of options see: sqlcmd /?
  If you experiance errors while connect to your instance under sqlcmd try some of the flowwing:
  - Make sure you have enabled the right protocols in the "SQL Server Configuration Manager", TCP/IP has the most change to succeed
  - Enable remote login in your "SQL Server Surface Area Configuration"
  - Enable remote DAC (dedicated administrator connection)
  - Try to connect with the -s parameter: such as 127.0.0.1,1433
  - Try to connect with a sql-user -Usa -P<password>
• Now type your select statement again: "select * from sys.sysdbreg"

So this are the real system tables. If you have taken a glance at the new system overview of MSSQL 2005 (http://download.microsoft.com/download/0/d/f/0dfe488e-a335-4480-8a8a-b405e32f4368/SQL2005_Sys_Views.pdf) you can see that the old workable system tables have been replaced for system views and quite a lot too! When you go back to the real tables by typing "select * from master.sys.objects$ where type='S'" you can see that there are not more than 51 system tables.
The real Oracle minded people must like te dollar sign after the objects.

Time to do where we came for. Now you have the capability to update the system tables by setting the right parameter in the sqlcmd with:
use [master]
go
sp_configure 'allow updates', 1
go
reconfigure with override
go

The values in the status of the sys.sysdbreg table still is an int but it is binary readed as you can see in the views mentioned earlier, every bit in the number stands for it's own database option and much more. You may take the warning for information: "Warning: System table ID 28 has been updated directly in database ID 1 and cache coherence may not have been maintained. SQL Server should be restarted." But don't forget to restart your sql server.

This time the binary table with the dboptions

So, update sys.sysdbreg set status=256 where name='<database_name>' marks the database as suspect. Another little trick is to place your own database among the system databases. Because Managment Studio looks at the system databases names and at the distributor option the only way is to mark your database as distributor with:
update sys.sysdbreg set category=16 where name='<database_name>'

The easiest way to to combine multiple database options is to sum the decimal. So a database with an status 1280 is suspect and readonly, like wise for the categorie. See also http://www.g-productions.nl/mssql.php?name=sysdbreg

Here's a list of the real system tabels in MSSQL 2005:

Stangly enough I can't find any primary keys in the system tables.