September 27, 2017 at 4:27 pm
Hi,
I have RDP access to windows server but i doesn't have access to SQL instance.
I doesn't have SA password.
How to connect to sql instance(SSMS) ?
How to reset the SA pasword ?
Thank you !!
September 27, 2017 at 4:42 pm
adisql - Wednesday, September 27, 2017 4:27 PMHi,I have RDP access to windows server but i doesn't have access to SQL instance.
I doesn't have SA password.
How to connect to sql instance(SSMS) ?
How to reset the SA pasword ?Thank you !!
Does anyone else have sysadmin access to the instance? That's the best way to handle that if you don't have access and/or sa password needs to be reset.
If no one has sysadmin access, you can find the steps to address that in the following article - it requires restarting the service:
Connect to SQL Server When System Administrators Are Locked Out
Sue
September 27, 2017 at 4:56 pm
Thanks Sue.
No one have sysadmin access to the instance.
September 27, 2017 at 5:15 pm
adisql - Wednesday, September 27, 2017 4:56 PMThanks Sue.
No one have sysadmin access to the instance.
Okay. That link above can walk you through the process.
If you don't mind doing it from the command line, I think it's easier and might be less confusing. There is a Microsoft blog that has the steps to do it that way - it works with 2016 even though it's written for 2012:
The “SQL Guy†Post # 30: How to Recover from a Lost SA Password in SQL Server 2012
They both basically do the same things so it's a matter of which ever way you are more comfortable with.
Sue
September 27, 2017 at 5:23 pm
Sue, I dont think the process (Connect to SQL Server When Sys...) works because my windows account wasn't added as sql login.
September 27, 2017 at 5:46 pm
adisql - Wednesday, September 27, 2017 5:23 PMSue, I dont think the process (Connect to SQL Server When Sys...) works because my windows account wasn't added as sql login.
You don't need a login to do it. Its assuming there are no sql or windows logins with access. The beginning of that article is confusing for a lot of people and it's not really necessary. It's just to run SQL Server Configuration Manager.
You can do this instead: On the server, run SQL Server Configuration Manager as an administrator. Right click, run as administrator.
And then go to number 4 in that list of steps.
Sue
September 27, 2017 at 5:50 pm
Thanks Sue.
Let me try it .
October 8, 2017 at 11:25 pm
This was removed by the editor as SPAM
October 8, 2017 at 11:25 pm
This was removed by the editor as SPAM
October 9, 2017 at 4:28 am
You can quickly and easily reset the "sa" account to regain access to a SQL Server instance using the "Reset-DbaAdmin" command from the PowerShell module dbatools.
# PowerShell 5.0 or higher
Install-Module dbatools
Import-Module dbatools
Reset-DbaAdmin -SqlInstance YourServer
#on named instance
Reset-DbaAdmin -SqlInstance YourServer\InstanceName
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply