Lost acces to sqlexpress

Question

Lost acces to sqlexpress

d.o.m.i.n.i.k 1

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

0 comments

Answer accepted by question author

YufeiShao-msft 7,156

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.

d.o.m.i.n.i.k 1 Reputation point

2021-12-14T10:01:34.037+00:00

Thank you so much it work :)

d.o.m.i.n.i.k 1

A few more steps I made was:

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn

sqlcmd -S lpc:[serverName]

1> create login [user] from windows
2> EXEC sys.sp_addsrvrolemember @loginame = N'user', @rolename = N'sysadmin';
3> ALTER SERVER ROLE [sysadmin] ADD MEMBER [user];
4> go

1 additional answer

Your answer

d.o.m.i.n.i.k 1 Reputation point

2021-12-14T10:01:34.037+00:00

Thank you so much it work :)
d.o.m.i.n.i.k 1 Reputation point

2021-12-14T11:38:36.113+00:00

A few more steps I made was:

cd C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn sqlcmd -S lpc:[serverName] 1> create login [user] from windows 2> EXEC sys.sp_addsrvrolemember @loginame = N'user', @rolename = N'sysadmin'; 3> ALTER SERVER ROLE [sysadmin] ADD MEMBER [user]; 4> go

Answer 1

Olaf Helper 47,631

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