BUILTINAdministrators in SQL Server on Linux

When I first started working with SQL on Linux one of the first things I did was to remove the default the [BUILTINAdministrators] login. This is pretty much standard practice with SQL on Windows as we don’t want administrators on the server to have automatic sysadmin rights into the SQL instance.

But this login makes no sense on Linux as there is no administrators group, so it should be dropped…right?

However, there’s a catch. In the Frequently Asked Questions about SQL Server on Linux here: –

Dropping Builtinadministrators for SQL Server on Linux breaks execution of some of the system stored procedures. We suggest to not remove or drop the Builtinadministrator account from SQL Server on Linux/containers.

One of these stored procedures is sp_readerrorlog. Let’s see what happens.

I’m connected into my SQL on Linux instance in SSMS and am going to drop the login: –

DROP LOGIN [BUILTINAdministrators];

And now try to read the error log: –

EXEC sp_readerrorlog;

And we get the following error: –

Argh! OK, let’s try to recreate: –

CREATE LOGIN [BUILTINAdministrators] FROM WINDOWS;

And we get another error: –

That’s pretty much expected but, how can we fix this?

To get the login back, we need to rebuild the system databases. Not ideal I know!

So stop the SQL instance: –

sudo systemctl stop mssql-server

Rebuild the system databases: –

sudo -u mssql /opt/mssql/bin/sqlservr --force-setup

Control+C once the setup has completed and restart the SQL instance: –

sudo systemctl start mssql-server

However, if we now try connecting to the SQL instance: –

Bah, we need to set the SA password again! Stop the instance: –

sudo systemctl stop mssql-server

Set the SA password: –

sudo /opt/mssql/bin/mssql-conf set-sa-password

Btw, if you see an error saying that the SA password was unable to be set, try logging in anyway as sometimes the errors lie!

Now start the SQL instance again: –

sudo systemctl start mssql-server

And now you should be able to log in and see that the [BUILTINAdministrators] login is back: –

You will now also be able to run sp_readerrorlog. The sysadmin rights can be removed from that login and sp_readerrorlog will continue to run, so that’s at least one thing that can be restricted.

Something to watch out for when working with SQL on Linux.

Thanks for reading!