Lost acces to sqlexpress

Question

I have a problem with permmisions. IN company we must changed our computer name and settings after migration to active directory. But I forget that my old PC name is admin in sql express. And I do not know sa password becouse in time of instalation I didn´t care of it and also dont have set option login by username and pass only win authentication. Now is the problem I can log in into server but Í cannot do anything even select. I try to add sysadmin to new PC but it always tells me you do not have permission. DO you have any idea how to get into this server or atleast get some backup of DB

Answer 1

Hi @d.o.m.i.n.i.k ,

Try to recover a lost SA password by starting SQL Server in single user mode

Open SQL Server configuration manager and select the service of SQL Server instance. Right-click and click on the Properties option. adding the parameter -m or –f in the startup parameters.
Restart the SQL Server service to start SQL Server in single user mode, Launch the Command Prompt and connect to SQL Server using SQLCMD
create a SQL server login using the below T-SQL script, like:

CREATE LOGIN NewSA WITH PASSWORD = 'Password@1234';  

Add the login NewSA to the server level role sysadmin

ALTER SERVER ROLE sysadmin ADD MEMBER NewSA   
GO  

and then remove the startup parameter -m or -f that is added and restart the SQL Server services

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If you have local Windows admin permissions, then you can fix it on your own (restore backup wouldn't help)
See Connect to SQL Server when system administrators are locked out