How do you directly modify the system catalogs in SQL Server 2017?

!!   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:

1. 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.

2. 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;  
   

3. 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."

Short answer: You don't.

Longer answer: Note that you should never do this on a production system. You can and likely will mess things up pretty bad, and MS will tell you to pound sand if you call them up asking for help with all of your unsupported/undocumented messing around.

With that out of the way, here we go.

First, you'll need to bring SQL Server up in single-user mode. You've tagged the question "linux", and I don't know if this works exactly the same way, but for the Windows version, just stop the SQL Server service, then launch it from the command line with the "-m" argument, e.g.:

cd "\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn"
sqlservr.exe -m

Once that's running, you have to connect to the dedicated-administrator connection (DAC) for the instance. You can use any tool you like, e.g. Management Studio or sqlcmd, but note that you can't connect Object Explorer in Management Studio, since it's running in single-user mode. In either case, specify the ADMIN: prefix for the server name. I think you can do ADMIN:(local) to connect to a local default instance.

After that, you can pretty much change what you want, but you'll have to figure out what the actual underlying tables are for certain system catalog views. For example, sys.database_principals. To see the view definition, switch to the "mssqlsystemresource" database (not normally visible), and use sp_helptext to see the code for any system views/procedures/functions.

USE mssqlsystemresource
GO
EXEC sp_helptext 'sys.server_principals'

You'll notice one of the tables this view references is master.sys.sysxlgns, which is probably a good place to start. You'll find a column named pwdhash in this table, which appears to store the hashed passwords.

When you're done tinkering, just go to your command window that's running SQL Server in single-user mode and hit Ctrl-C to shut it down.

Remember, don't do this to a production system, ever, unless Microsoft support has given you specific instructions otherwise. Other than that, have fun, and make sure you've got backups for when you wreck the instance!