Unable to add admin server role in SQL express 2019

Question

Hi everyone,

I installed SQL server express 2019 on my laptop and try to create a new database but failed. then I realized that I may not have the right role. When I install the SQL express 2019, it didn't ask me to configure password for SA.

When I try to add server role, it pop up the error message. From my screenshots, you even can see the sa user is disabled. Can some tell me how to fix it ? many thanks.

Answer 1

As APoblación says, you should have been able to install SQL Server without specify an admin user, but the screen makes it clear there is only BUILTIN\USERS and sa. And sa is disabled.

Check this article: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-ver15
Once you have started SQL Server in single-user mode, connect with SQLCMD and run

   ALTER SERVER ROLE sysadmin ADD MEMBER YOURDOMAIN\YourUser  

Leave sa disabled.

Answer 2

When you install SQL Server, there is a step where the installer asks who will be the administrator for SQL Server. Installation gets blocked at this step until you add at least one user, so you must have configured something here. There is a button for adding the current user who is performing installation. The only sysadmin will be that user unless you explicitly add someone else.

SQL server can work in two modes: Windows-only authentication and Mixed authentication. The default unless you change it during installation is Windows Only. When SQL Server is installed in this way, it only accepts Windows users. Any users defined as a SQL Login and Password, such as sa, will not work.
It is possible to switch into Mixed mode from the Security tab in the instance properties in SQL Server Management Studio. But to do this, you first need to log in as a user who has enough privilege to make this change, which by default will only be the one that you configured for this purpose during installation.