SQL Server 2017 – How to Modify System Catalogs Directly

catalogslinuxsql serversql-server-2017system-tables

NOTE: I understand the risks involved, and the possibility of destroying a production system by doing this. I'm interested in doing it anyway.

Whenever I try to play with system catalogs, I gets these weird errors,

UPDATE sys.sql_logins
SET password_hash = PWDENCRYPT('pass')
WHERE name = 'sa';

Error produced,

Msg 259 […] Ad hoc updates to system catalogs are not allowed.

I've tried numerious ways to take the training wheels off,

sp_configure 'allow updates',`
go
reconfigure
go

But, I can't figure out the right option…

It even says in this answer:

this is no longer possible (at least not without jumping through a ton of additional hoops beyond just an sp_configure option – it's not something you ever want to do on a production system), and all of the system catalog is now exposed through read-only views like sys.objects.

Well, what if I want to jump through those hoops. How can I do it?

Best Answer

`!! BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM !!`

The server configuration option of allow updates was rendered non-functional starting in SQL Server 2005. The documentation states that it does not error, but won't actually allow for any direct updates to system catalog tables.

The only way to achieve this now, BEING FULLY AWARE THAT THIS IS DANGEROUS AND IS NOT RECOMMENDED, is to use a Dedicated Admin Console (DAC) connection.

First you will need the actual Table name that you want to update. The name that you SELECT from is just a system catalog View, not the real Table. I usually use the following:

EXEC sp_helptext N'sys.{some name here}';

Then, do the following:

Restart the instance in Single-User mode with
```
sudo systemctl stop mssql-server
sudo -u mssql /opt/mssql/bin/sqlservr -m
```
Just to be clear: this step is to allow for system catalog updates. Connecting to the DAC itself for doing other operations does not require Single-User mode.
Connect via Dedicated Admin Console connection with sa or some other sysadmin Login. You can do this in a SQLCMD interactive session by running the following in a Command Prompt window:
```
sqlcmd -S admin:localhost -U sa
```
If the Linux SQLCMD doesn't support this for some reason, you can enable remote DAC connections and then later use a Windows machine to dug that DAC is working. You can enable DAC on Linux with,:
```
EXEC sp_configure 'remote admin connections', 1;  
RECONFIGURE;  
```

In that DB, try something like the following:

UPDATE sys.{whatever_name_you_found} {enter}
SET [some_column] = {some_value} {enter}
WHERE [some_other_column] = {the_better_be_correct_or_else_ID}; {enter}
GO {enter}

It won't execute the statement until you put in the GO {enter}.

DANGER WILL ROBINSON!!

Please be cautious when directly editing system catalog tables, and do not get too comfortable with the idea of doing it. This is something that should be done only if there is absolutely no other way of fixing a problem (such as the case here). There are probably several reasons to avoid making direct edits, but the two that initially come to mind are:

Much like the data-models that we create, there are likely rules and workflows for how things work that we are unaware of (e.g. denormalization, "business" rules governing the state of data across various tables, etc)
Quite likely making direct edits voids Microsoft's responsibility to assist you if you run into problems and have a support contract (I have not seen the terms of the Support agreement(s), but I have a hard time believing that such language would not be in there).

@Paul Randal confirmed in a comment: "manually editing a system table irrevocably sets a flag in the boot page of the database that marks your database as having been edited in this way, and CSS might decide to not help you if you subsequently have problems with that database."

You can't. The feature is disabled in 2017 RTM.

That said, you can...

Using AdventureWorks:

CREATE VIEW dbo.TH
WITH SCHEMABINDING
AS
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
GROUP BY P.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

Changes to the underlying tables are not immediately reflected in the view (as is normally the case with SQL Server). Likewise, data modifications against the underlying tables do not have to maintain the snapshot indexed view.

To refresh the view contents, one needs to call one of the new stored procedures:

EXECUTE sys.sp_refresh_single_snapshot_view
    @view_name = N'dbo.TH',
    @rgCode = 0; -- don't know what this is for yet

This produces the execution plan:

This likely won't work for you, because either an undocumented trace flag is needed, or you need to do the particularly nasty thing I did: writing to the memory location holding the feature flag (using a debugger) to enable this feature.

If you're curious, the feature flag is the byte at sqllang!g_featureSwitchesLangSvc+0x10f. It is checked during sqllang!SpRefreshSingleSnapshotView.

If you want to play along, and are fully prepared to accept the consequences of hacking about in SQL Server's code while it is running, and using a feature that Microsoft does not think is ready yet:

Attach a debugger to the SQL Server 2017 process. I use WinDbg.
Set a breakpoint:
```
bp sqllang!SpRefreshSingleSnapshotView
```
Resume SQL Server using the Go command (g)
Create the view above, but not the unique clustered index yet
Run the sys.sp_refresh_single_snapshot_view command above
When the breakpoint is hit, step through until you see the code line:
```
cmp byte ptr [sqllang!g_featureSwitchesLangSvc+0x10f (00007fff`328dfbcf)],0
```
The offset may be different in other builds, for example in 2017 RTM CU3 it is sqllang!g_featureSwitchesLangSvc+0x114
The memory address inside the parentheses may be different. Use the one you see.
Use the display memory command to see the current value at the memory address you found:
```
db 00007fff`328dfbcf L1
```
This should show a zero, indicating that the feature is disabled.
Change the zero to a one, using the enter values command (again with your memory address):
```
eb 00007fff`328dfbcf 1
```
Disable the breakpoint and resume running SQL Server.
The feature is now enabled.
Build the unique clustered index on the view.
Play around.

Note SNAPSHOT_MATERIALIZATION allows us to materialize a snapshot of a query specification that ordinarily could not be indexed, for example the below uses MAX:

CREATE VIEW dbo.TH2
WITH SCHEMABINDING
AS
SELECT TH.ProductID, MaxTransactionID = MAX(TH.TransactionID)
FROM Production.TransactionHistory AS TH
GROUP BY TH.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH2 (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

Result:

Commands completed successfully.

Best Answer

!! BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM !!

DANGER WILL ROBINSON!!

Related Solutions

SQL Server 2017 – How to Completely Remove SQL Server 2017 on Ubuntu

SQL Server 2017 – How to Create a View with SNAPSHOT_MATERIALIZATION

You can't. The feature is disabled in 2017 RTM.

Related Question

`!! BE SURE TO READ THE WARNING, IN BOLD, AT THE BOTTOM !!`