Lost acces to sqlexpress

d.o.m.i.n.i.k 21 Reputation points
2021-12-14T08:31:08.563+00:00

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

SQL Server | Other
0 comments No comments
{count} votes

Accepted answer
  1. YufeiShao-msft 7,146 Reputation points
    2021-12-14T09:26:37.11+00:00

    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.


1 additional answer

Sort by: Most helpful
  1. Olaf Helper 47,516 Reputation points
    2021-12-14T09:16:30.513+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.