HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Not Able to Enable sa Account in SQL Server 2019 Express after Login with Windows Authentication Mode

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
afterenableexpresssqlwithmodelogin2019accountauthentication

Problem

I have installed SQL Server Express 2019 on my machine and after installation, I am able to login into server using the Windows Authentication method like

And as you can see the sa account has been disabled by default

So I tried to enable the sa account like

But I am getting this error message

Cannot alter the login 'sa', because it does not exist or you do not have permission.(Microsoft SQL Server, Error:15151)

Even when I tried to change the server authentication mode from Windows Authentication mode to SQL Server and Windows Authentication mode at

I am facing this error!

The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229)

So it looks like I have no/not granted any permission to do any anything! when login with Windows which apparently is my only option to get into server!(this is also happening when I tried to create new user!)

Can you please let me know what I am doing wrong and how I can handle this to act as an administrator for DB when I login?

I have already seen and tried this post, so please kindly, if you are not sure about the solution do not close, down vote or redirect me again to this post?

Solution

It looks like you don't have enough permissions to manage logins or anything else on the server.
You can verify this by running this command:
SELECT IS_SRVROLEMEMBER('sysadmin')


It should return 1 if you are a sysadmin, 0 otherwise.
If you are not a sysadmin, you need to make yourself an admin. Here is how you do it:

  • Stop the SQL Server service



  • Copy the path to the SQL Server executable from the service window. Open a cmd window (as admin) and run SQL Server interactively in single-user mode:



\sqlservr.exe" -m -s SQLEXPRESS

-
Open another cmd window and run sqlcmd:
sqlcmd -S (local)\SQLEXPRESS

-
Add yourself as sysadmin by running this command in sqlcmd:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\youruser]
GO


If it fails it means that you need to add your user explicitly in that sqlcmd window:
CREATE LOGIN [DOMAIN\youruser] FROM WINDOWS;
ALTER SERVER ROLE [sysadmin] ADD MEMBER [DOMAIN\youruser];
GO


-
Press Ctrl+C on the cmd window where you have sql server running in single-user mode.

-
Start SQL Server process normally.

This should do.

Context

StackExchange Database Administrators Q#322588, answer score: 3

Revisions (0)

No revisions yet.