|Working with System
Tables: Beyond the Basics
With a few exceptions, the
information used by SQL Server is stored system tables. Some
relatively low level stuff such as storage allocation maps and
memory distribution are managed by the kernel and are never
materialized as SQL tables. The only visibility we DBAs have -
if at all - to such data is through DBCC command and external
tools. A few other kinds of information are similarly managed in
the kernel, but for our convenience are manifest as a special
class of view known as virtual tables. Virtual tables are never
stored on disk but are available when ever the SQL Server
Service is running. Processes, locks, and performance data - as
master.dbo.sysprocesses and master.dbo.syslockinfo,
and master.dbo.sysperfinfo - are examples of data
available as virtual tables. For performance, manageability, and
other proprietary justifications there is a small mount of data
we cannot access though for the most part the actions of SQL
Server are built around the same sort of data driven processing
we use in our applications. As they like to say in Redmond: SQL
Server eats its own dog food.
The majority of the system tables
are stored much like the tables you and I create for our
application. The database location of system tables is based on
scope of function. System tables that have a critical server
wide interest are primarily stored in the master
database. Others that are used for SQL Agent, backup/restore,
replication, log shipping, DTS, and maintenance are stored in
the msdb database. SQL Server can run without the tables
stored in the msdb database. SQL Server cannot run
without the tables stored in the master database. In
addition to the critical tables in master and the
supporting tables in msdb, each database - including the
system databases master, model, msdb,
tempdb, and dist - have a structurally identical set
of tables used to manage and support database objects, access,
dependencies, and file groups. All system tables are owned by
"dbo" in SQL Server 2000.
In SQL Server 2005 a change is
coming. The schema for system tables will be "sys" - fact is the
whole concept of schema being different than owner is coming.
For example the table in each database dbo.sysobjects
will become a view. Another view also comes along with
the same purpose: sys.objects. Through a backward
compatibility feature we will still be able to access the data
using dbo.sysobjects, but dbo.sysobjects will
become a view rather than a table. Something tells me the
details aren't quite all worked out here because in beta one
there are actually a couple of duplicate names sys.objects.
I'm going to keep watching for more details on system tables in
SQL 2005, but in the mean time I'll just have to accept that
there will be some changes.
From time to time you may have
seen an offer to obtain a wall chart that shows the SQL Server
2000 system tables. If you can get your hands on one of these
charts I highly recommend it. If not, there is an excellent -
perhaps even better than the wall chart - alternative available
for download from Microsoft: the
SQL Server System Table Map.
The is a help document. I suggest saving it on your workstation,
adding a link to it from the Microsoft SQL Server program group,
and referring to it often. Understanding the structure, data,
and workings of the system tables is fundamental to the ability
of a SQL Server DBA to support her database users. Hopefully we
will see a similar system map for SQL Server 2005 in the near
future. One thing for certain is that it won't be the same as
SQL Server 2000.
Of course, the first thing you
will want to understand is that system table data should not be
modified. Changing system data is no less risky than editing the
registry or the boot.ini file. Good administrators know not to
modify such data as rule, take all necessary precautions before
doing so in the rare case that it is necessary, and don't
confuse the high risk of changing data and the huge benefit of
looking at the data in the day to day operation of a system.
Good administrators also know to steer the application
developers away from directly using system tables in
applications. While any system table column that is documented
in Books Online is technically fair game in application
development, we have no control over how system tables might
change in future releases. In fact, in SQL Server 2005 there is
a major upheaval in the system architecture. Fortunately
Microsoft is assuring that the changes are supported by a lot of
backward compatibility smoke and mirrors. Nonetheless, moving
forward it will be more difficult for developers - especially
new to SQL Server developers - to make sense of the variety of
system table access that is bound to evolve. Best practice for
applications has been and will remain to use information schema
views, system stored procedures, T-SQL statements and functions,
DMO/SMO, and database application programming interfaces (API)
catalog functions to access system table data.
On the off chance that you do
need to modify a system table, there are a couple of important
and easy rules to follow. The first is that you must be logged
into the SQL server as a member of either the sysadmin or
serveradmin server roles, and the second is that there is
a very specific T-SQL sequence necessary to properly complete a
change. Below is an example to help explain the sequence. The
example is somewhat interesting if you find your self with the
same login having different SIDs on different SQL Servers and
you must frequently restore backups from one SQL Server instance
to the other. If, as in this example, you reset the SID on one
of the SQL Servers (the development box) to match the other (the
production box) then you can eliminate the repetitive step of
"fixing" the user/login mapping after each restore. Now you
could drop the login and re-add it with the correct SID, but
that would involve the loss of all permissions set for users
mapped to that SID. Changing the login's SID in the
master.dbo.sysxlogins table on one of the machines is a less
disruptive approach. To make the change it is first necessary to
enable updatability of the system tables in master with the
sp_configure system stored procedure and then to allow that
updatability state to change without stopping and starting the
SQL Server service by issuing the RECONFIGURE statement.
Notice that the RECONFIGURE cannot happen in the same
batch as the actual update statement. Here I use dynamic SQL to
execute the update in a new batch. Then once the system table is
changed I turn off the updatability and once again issue the
RECONFIGURE statement so that system tables are not
inadvertently modified from this time.
, @user varchar(30)
, @login varchar(30)
, @sql varchar(200)
set @newsid = 0x4FEF7B3DA31E564A85C8F20CA71BD1E4
set @user = 'test'
set @login = @user
set @sql = 'use ? if exists (select 1 from sysusers where
name = '''
+ @user + ''') exec sp_change_users_login ''Update_One'','''
+ @user + ''',''' + @login + ''''
if not exists (select 1 from master.dbo.sysxlogins where sid
exec master.dbo.sp_configure 'allow updates', '1'
RECONFIGURE WITH OVERRIDE
N'update master.dbo.sysxlogins set sid = @newsid where
name = @user'
, N'@newsid uniqueidentifier, @user varchar(30)'
exec master.dbo.sp_configure 'allow updates', '0'
RECONFIGURE WITH OVERRIDE
exec master.dbo.sp_msforeachdb @sql
print 'SID is already in use on this server'