Let's Play Block The DBA

What?! That can't be done, can it? The final answer is no. However, you can certainly block an unknowledgeable DBA with the techniques I will describe in this article. The same techniques will block you and other users from forgetting business rules and doing tasks you shouldn't or simply block you from accidentally dropping the wrong table.I'm quite sure there are other ways to do the same things, ways that are considered better. My goal in this article is to use some extreme methods which could take a sequence of steps to undo and certainly will require some knowledge about system tables.

The ways I plan to do this mean modifying system tables (also known as system catalogs) which many DBAs frown upon. Microsoft recommends you don't modify system tables. Also modifying system tables can mean Microsoft won't help you if a problem is related to said modifications. Having said this I likely brand myself as a rogue developer. I share them here now to show you what can be done should you feel the need to use these methods, say in your development environment as a joke. You can automatically include all the techniques I describe in this article in your list of worst practices and as such should not seriously consider any of them as viable solutions.

Sample Code

CREATE TABLE [dbo].[TestTable] (
            [col1] [char] (10) NULL
) ON [PRIMARY]
GO

Example 1:Block DROP TABLE Command

Here is a way to completely block the DROP TABLE command. First you must execute the following commands on your test server to allow you to make changes to the system tables. Later in this section I will give you the code to disallow changes to the system tables.

EXEC sp_configure 'allow updates', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

Now execute the sample code given in the previous section. Execute the below code to mark the table you created as a table that is replicated:

UPDATE o SET replinfo = 1
FROM sysobjects o
WHERE name = 'TestTable'

SQL Server will block you from dropping TestTable since it is now considered a replicated table.Upon executution of the command “DROP TABLE TestTable” you will receive the following error:

Server: Msg 3724, Level 16, State 2, Line 1
Cannot drop the table 'TestTable' because it is being used for replication.

I don't think I would ever mark all my user tables as replicated tables, however I have often considered changing some of them. This would definitely avoid the mistake of issuing a DROP TABLE command on a table in production when you thought you were connected to your development SQL Server.

Use this code to return the table to its normal state:

UPDATE o SET replinfo = 0
FROM sysobjects o
WHERE name = 'TestTable'

Now lets disallow modification to the system tables. Execute the below code:

EXEC sp_configure 'allow updates', '0'
GO
RECONFIGURE
GO

Feel free to use the SELECT, DELETE, UPDATE, and INSERT commands. They will all work.To

EXAMPLE 2:Block New Database User

For this example we'll do what you've either read or heard is not possible. We're going to place a trigger on a system table. What?!Don't blink or you might miss something.Execute the code from example 1 to enable changes to system tables. Now execute the following code to let you place a trigger on the sysusers database:

UPDATE o SET xtype = 'U'
FROM sysobjects o
WHERE name = 'sysusers'

Please note that this change does not completely cause SQL Server to count the table as a user table.It will still show up as a system table in Enterprise Manager. However, it will allow you to place a trigger on the table by using the following code:

CREATE TRIGGER BlockNewUsers
ON sysusers
AFTER INSERT AS
 
            DELETE sysusers FROM sysusers s INNER JOIN inserted i ON s.uid = i.uid
 
            PRINT 'New users not allowed.  Please ignore words on next line.'

Execute the below update to return the sysobjects table back to normal:

UPDATE o SET xtype = 'S'
FROM sysobjects o
WHERE name = 'sysusers'

Now execute the code that will disallow changes to system tables. Now create a new login for your SQL Server or use an existing login. Copy the below code and replace ‘u1' with the login you are going to use and execute the code:

EXEC sp_adduser 'u1'

You should see the following in the Query Analyzer message window:

New users not allowed. Please ignore words on next line.
Granted database access to 'u1'.

You can view the users via Enterprise Manager or view the table sysusers and you will not find the new user since the trigger did fire and deleted the user after it was added. You can drop the trigger at any time without modifying the xtype of the system table.

Example 3:Creating Read-Only Tables

This example could be the one I view as most likely to be useful should you have a need for a read only table that even DBAs can't change unless they undo what I show you here. Once we're through you won't be able to make any structural changes nor changes to the data in the table. You will be able to view that data in the table and that is about it.

Again you will need to run the code from example 1 to enable changes to the system tables. Now, rerun the sample code given at the beginning of this article. Execute the following INSERT statement to put some data in the table TestTable:

INSERT INTO TestTable (col1)
SELECT 'A'
UNION
SELECT 'B'
UNION
SELECT 'Robert'

Once done, you can execute the below code so that SQL Server will count TestTable as a system table:

UPDATE o SET xtype = 'S'
FROM sysobjects o
WHERE name = 'TestTable'

Now execute the code from example 1 to disable changes to system tables. Now you can run DELETE, UPDATE, and INSERT statements all you want and all you will get are errors like this one:

Server: Msg 259, Level 16, State 2, Line 1

Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.

You won't be able to add nor modify columns. You can't add a primary key, nor a trigger, nor an index to the table any more.This technique will also block the DROP TABLE command and the TRUNCATE TABLE command.

Conclusions

In this article I have shown you how to modify system tables (also called system catalogs).I have shown you a few ways you can modify the system table called sysobjects to block certain activities that a user with SA privileges could normally do. I have also indicated that these techniques are best used as jokes on a development SQL Server even though there is a slim chance they could be useful for other uses.Once more let me stress that everything described in this article can be considered as worst practices and are shared to give you a brief look into a system table and how SQL server blocks certain activities via replication and the system tables.

I look forward to your comments even if you do choose to blast me verbally for daring to share such information in the way I did in this article. Feel free to let me know what you think.