Access SQL instance without sa password

Question

Access SQL instance without sa password

lduvall

SSCarpal Tunnel

Points: 4869
More actions
June 3, 2015 at 9:52 am

#301231

All, I've done online research and I think I'm basically out of luck here, but I wanted to check anyway. I came into a new job where the PC I was assigned already had a named instance of SQL that appears to have been installed using the old employee's Windows credentials. Those credentials no longer exist & I have no idea if there was an sa password set up for the server. (I suspect there was not.) Is there any way for me to connect to this server somehow or do I truly have to un-install/re-install?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

As far as I know you are out of luck.

Are you sure the credentials were removed or was that account just disabled? If possible you could work with the AD administrator (or whoever handles that where you work) and connect to that instance using the old credentials grant yourself access and re-disable the credentials.

If the account was truly deleted perhaps the AD admin could recreate the account, you could do what I just described and then delete the account. I don't know if this would work (as the account would get a new SID) but it's worth a try.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lowell SSC Guru Points: 323518 More actions · Answer 2

if you can rdp or login directly to the server, then you could stop and start the service in sinlge user mode, regardless of whether you are a sysadmin on the instance.

from there, since you came in as a local user, you can add yourself to SQL as a sysadmin, set the sa password to whatever you want, and you'd be all set.

google "sql server 2012 locked out", or use this script that i like to point folks to that was origianlly on the microsoft site before they moved it,

https://gist.github.com/wadewegner/1677788

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Lowell (6/3/2015)
if you can rdp or login directly to the server, then you could stop and start the service in sinlge user mode, regardless of whether you are a sysadmin on the instance.
from there, since you came in as a local user, you can add yourself to SQL as a sysadmin, set the sa password to whatever you want, and you'd be all set.
google "sql server 2012 locked out", or use this script that i like to point folks to that was origianlly on the microsoft site before they moved it,
https://gist.github.com/wadewegner/1677788

Lowell, this instance is not on a server, it's on his local machine (PC).

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 4

same thing then, right?

if he is a local admin on the PC in question, he can run the script and take over the SQL instance(s).

if he had 3 instances(ie .\SQLEXPRESS, .\SQL2008R2 and .\SQL2014 named isntances) , he'd need to run the script three times, and name the instance to take over each time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Lowell (6/3/2015)
same thing then, right?
if he is a local admin on the PC in question, he can run the script and take over the SQL instance(s).
if he had 3 instances(ie .\SQLEXPRESS, .\SQL2008R2 and .\SQL2014 named isntances) , he'd need to run the script three times, and name the instance to take over each time.

Sorry Lowell, but I hadn't looked at the details of your solution. I agree, local or RDP should not matter.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 6

I've reached out to the "wing" of the company that might tell me if they have disabled my predecessor's account & could temporarily re-instate it so that I could assign myself that way. I'm not optimistic that they will help me.

I am eager to try that script, but I'm not sure exactly how I use it. Do I just copy/paste it into a .txt file, change the inputs to match the ones I need, re-save as a .bat and then double-click to execute? I know this is a rudimentary question to you, but I am really just a T-SQL developer. I don't delve into cmd line or OOP or anything like that, so I need help to "dumb it down" for me. I'm definitely eager to try.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 7

Lowell's logic is spot on. Not sure what your search criteria were, but if you search google for "access sql server administrators locked out" you get Microsoft's own Books Online article to tell you how to do it.

Connect to SQL Server When System Administrators Are Locked Out

Joie Andrew
"Since 1982"

Lowell SSC Guru Points: 323518 More actions · Answer 8

lduvall (6/3/2015)
I've reached out to the "wing" of the company that might tell me if they have disabled my predecessor's account & could temporarily re-instate it so that I could assign myself that way. I'm not optimistic that they will help me.
I am eager to try that script, but I'm not sure exactly how I use it. Do I just copy/paste it into a .txt file, change the inputs to match the ones I need, re-save as a .bat and then double-click to execute? I know this is a rudimentary question to you, but I am really just a T-SQL developer. I don't delve into cmd line or OOP or anything like that, so I need help to "dumb it down" for me. I'm definitely eager to try.

it's a .cmd file, same behaviour as a .bat file.all black window/command line.

when you run it, you are prompted to type the name of the INSTANCE:

MSSQLSERVER for the default, or SQLEXPRESS or whatever the name is.

it does all the steps outlined in the article Joie Andrew mentioned, but in a scripted fasion:

stops the isntance.

starts the isntance in single user mode.

adds the current domain user to that isntance

stops the isntance .

restarts the isntance in normal mode.

i've used that exact script dozens of times; doesn't work on clusters without some extra work, but otherwise it''s aawesome.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric M Russell SSC Guru Points: 125562 More actions · Answer 9

If it were me, and we're talking about a local developer instance, then I'd just uninstall SQL Server 2012, and then re-install SQL Server 2014. It will support all the 2012 functionality plus you can experiment with Clustered ColumnStore and In-Memory tables. If needed, you can also attach the old 2012 .mdf database files to the new 2014 instance.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 10

Thanks so much everyone! I got it working using Lowell's script. Your help is very much appreciated!