If you have accidentally removed the sysadmin role from all your logins and cannot perform administrative tasks, you can regain access through the following methods.
Stop the SQL Server service:
Start SQL Server in Single-User Mode:
C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn).sqlservr.exe -m
Connect to SQL Server:
sqlcmd or SQL Server Management Studio (SSMS). For sqlcmd, use:sqlcmd -S localhost -E
Add sysadmin Role to a Login:
sysadmin role to a login (replace YourLogin with the login name):CREATE LOGIN YourLogin WITH PASSWORD = 'StrongPassword';
GO
ALTER SERVER ROLE sysadmin ADD MEMBER YourLogin;
GO
Restart SQL Server in Normal Mode:
sqlservr.exe, you may need to close the Command Prompt window).If the SQL Server service account is a member of the Windows local administrators group, you can use this to regain access:
Stop SQL Server Service:
Start SQL Server in Single-User Mode:
Connect to SQL Server Using SSMS:
Add sysadmin Role to a Login:
CREATE LOGIN YourLogin WITH PASSWORD = 'StrongPassword';
GO
ALTER SERVER ROLE sysadmin ADD MEMBER YourLogin;
GO
Restart SQL Server in Normal Mode:
Connect Using DAC:
ADMIN: prefix with the server name to connect using the Dedicated Administrator Connection (DAC), e.g., ADMIN:localhost.Add sysadmin Role to a Login:
CREATE LOGIN YourLogin WITH PASSWORD = 'StrongPassword';
GO
ALTER SERVER ROLE sysadmin ADD MEMBER YourLogin;
GO
sysadmin role to avoid being locked out.By following these methods, you should be able to regain administrative access to your SQL Server instance.