patternsqlMinor
Not Able to Enable sa Account in SQL Server 2019 Express after Login with Windows Authentication Mode
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
And as you can see the
So I tried to enable the
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
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?
Windows Authentication method likeAnd as you can see the
sa account has been disabled by defaultSo I tried to enable the
sa account likeBut 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 atI 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:
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:
-
Open another cmd window and run sqlcmd:
-
Add yourself as sysadmin by running this command in sqlcmd:
If it fails it means that you need to add your user explicitly in that sqlcmd window:
-
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.
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.