April 19, 2012 at 2:53 am
Changing jobs. My personal desktop box hosts my development sql server instances. My domain account from the old company that was mapped to sa is inaccessible now on my home workgroup. Short of uninstall / reinstall sqlexpress are there any other ways to get control of the sa account? If I could just reset the password I'd be a happy camper.
.
April 19, 2012 at 2:56 am
unsure if this works on SQLExpress but you could start SQL in single user mode and as long as your an administrator of the actual workstation you will get SA access to SQL which will allow you to add in your login or change the SA password.
Think its the -m or -c switch you want, but best to check the startup parameters.
April 19, 2012 at 6:36 am
anthony.green (4/19/2012)
Think its the -m or -c switch you want
It's both 😉
-m starts the instance single user and -c shortens the command prompt startup time as it bypasses the calls to the service control manager (Services.MSC).
change to the Binn directory for the instance you wish to start and use the following to start the instance single user
sqlservr -c -m -Ssqlserverinstname
Reset the SA password or just add your new windows account as a login and grant sysadmin server role, either will work
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 19, 2012 at 9:29 am
Get Back into SQL Server After You've Locked Yourself Out
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 19, 2012 at 10:50 pm
Thanks for the info. I had no luck though. I tried starting the instance with the recommended command line switches, and all I got was an empty dialog box with a red X and no message. When I started digging into my configuration, I discovered it was a bit of a mess. Old SQL 2008 instances that I wasn't even aware of, etc., so I decided it was time for some cleanup. Decided to go the uninstall route. I really didn't have anything to lose so no biggie. Perfect excuse to install SQL 2012.
If anyone has any info about the empty, red X dialog, it might be useful for the next guy though.
Thanks.
.
April 19, 2012 at 11:52 pm
You need to detail exactly the steps you took and any errors that you received.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 24, 2012 at 1:30 pm
I'm assuming you started up a command prompt running as administrator, correct? If you didn't you need to if UAC is turned on. Then start sql server from the command line using the appropriate switches as given. You should see a lot of output that looks very similar to what you see in the SQL Server log.
Then, using SSMS or another tool, connect to that instance of SQL Server using an account that is an administrator on the box. Remember, you only get a single connection, so if Object Explorer is open and you're trying to enter a T-SQL command, that's not going to work. So you probably want to set SQL Server Agent to manual and stop it, as well as any third party services which connect to SQL Server. Once you're in, make the changes. Then shutdown SQL Server.
This should put you back to a command prompt in the command window. If that's the case, use the services applet to restart SQL Server. Now you should be able to log in based on what you changed.
K. Brian Kelley
@kbriankelley
April 25, 2012 at 3:24 am
K. Brian Kelley (4/24/2012)
Then, using SSMS or another tool, connect to that instance of SQL Server using an account that is an administrator on the box. Remember, you only get a single connection, so if Object Explorer is open and you're trying to enter a T-SQL command, that's not going to work. So you probably want to set SQL Server Agent to manual and stop it, as well as any third party services which connect to SQL Server.
I usually use SQLCMD to make the connection, because you're guaranteed to only have one connection active when you use that. (And he's using Express Edition, so no problem with the Agent... :-)).
April 25, 2012 at 8:32 am
paul.knibbs (4/25/2012)
because you're guaranteed to only have one connection active when you use that. (And he's using Express Edition, so no problem with the Agent... :-)).
That's just it you're not! If you have an application that's constantly trying to connect and does so as soon as you restart the instance single user, you won't be able to connect as the single connection has already been used 😉
In SQL Server 2008 and later you can force SQL Server to only accept connections from the program you specify. For a SQLCMD prompt you would use
-m"sqlcmd"
To connect through SSMS use
-m"Microsoft SQL Server Management Studio - Query"
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 11, 2012 at 8:01 am
alvirasingh2012 (5/11/2012)
hello dude,htis is my same problem .if u found any solution pls reply me.
spam reported
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply