4

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

enter image description here

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

enter image description here

So I tried to enable the sa account like

enter image description here

But I am getting this error message

enter image description here

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

enter image description here

I am facing this error!

enter image description here

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?

Improve this question

2 Answers 2

Reset to default
4

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:

  1. Stop the SQL Server service
  2. 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:

<path to sql server executable>\sqlservr.exe" -m -s SQLEXPRESS

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

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

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

  2. Start SQL Server process normally.

This should do.

Improve this answer
1
0

launch the SQL server Management Studio using right click > Run as administrator and log in with Windows Authentication mode, this way you would have the access to do changes e.g. enable and change pw of the user sa, configure the server authentication etc. it worked for me.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.