SQL Help - Reset SA Password

Pho3nix

Pho3nix

The Legend
Joined
Jul 31, 2009
Messages
32,825
Reaction score
3,033
Location
On the toilet
Hi all,

So a junior ran a script to add permissions and ended up removing the admin accounts from SQL and we now can't log on :mad:
It's a Dev-QA server so it isn't a train smash but we would like to get it back up.

Any one know of a way to reset the SA account password as we currently don't have it and apparently no one else does.
 
Pho3nix said:
Hi all,

So a junior ran a script to add permissions and ended up removing the admin accounts from SQL and we now can't log on :mad:
It's a Dev-QA server so it isn't a train smash but we would like to get it back up.

Any one know of a way to reset the SA account password as we currently don't have it and apparently no one else does.
Click to expand...

So he wanted to add, and he removed? :o
Maybe you should teach him to read his code before he executes it.
 
froot said:
So he wanted to add, and he removed? :o
Maybe you should teach him to read his code before he executes it.
Click to expand...

He isn't my minion :p
One of the new kiddies from a graduate program, his senior gave him a light tongue lashing.
 
You have to shutdown sql, login via terminal with sql safe mode where you can edit the user account table. Flush permissions and reboot sql.
 
a Light tongue lashing?

That little minion would have received a ridiculous beatdown from me...
 
ToxicBunny said:
a Light tongue lashing?

That little minion would have received a ridiculous beatdown from me...
Click to expand...

Light as it isn't a train-smash. Backups are available of everything, problem is the billable time wasted fixing this schit. Lead Dev had a lol.
We've all been there, remember turning off a production server by mistake when I started out :o
 
I've sent a request to see if we have a backup of the entire box, don't really want to re-do a SQL install at the moment.
 
Do this...

Go to SQL Server Configuration Manager and click on SQL Server 2005 Services. Click on desired SQL Server instance and right click go to properties. On the Advance table enter param ‘-m;‘ before existing params in Startup Parameters box.

Make sure that you entered semi-comma after -m. Once that is completed, restart SQL Server services to take this in effect. Once this is done, now you will be only able to connect SQL Server using sqlcmd

Create yourself a login to get into the box and give it sysadmin roles.

CREATE LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>';
GO
EXEC sp_addsrvrolemember 'LoginName', 'sysadmin';
GO

You can also alter the sa password from the sqlcmd console
ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';
 
Does that work HyperVDm and what does the -m switch represent?

On a different note but not really helpful now, as a standard preactice by me when doing a installation, I always disable the SA account. Set mixed mode and allow the lead access by default with high privledges, set a account for devs and away you go. I know a few times folks have tried to access a sql server that was accidently public due to a flaw by the net admin and killing the SA account gives you a warm feeling when you see some poor sod of a hacker trying to break in.

I'd also seriously consider having a junior work on a personal instance while running/learning scripts. Work on a production or even dev server should be monitored as it could impact production or the team.

As to the punishment, yep I agree, we've all been there. Basic rule of thumb for me is if someone tells you about it. If they try hide it, then come down like a ton of bricks but admiting a mistake is human and expected.
 
The -m makes it start in a kind of a management mode. It gives you god mode when connecting through sqlcmd :-)

Dont ever disable the sa account unless you are working with Express versions, you are opening yourself to a world of problems when using Agent Jobs. Rather make the password very strong. If you are getting attacks on your SQL server you need to talk to the security team. SQL servers should be isolated behind FortiGate.
 
Kosmik said:
Does that work HyperVDm and what does the -m switch represent?

On a different note but not really helpful now, as a standard preactice by me when doing a installation, I always disable the SA account. Set mixed mode and allow the lead access by default with high privledges, set a account for devs and away you go. I know a few times folks have tried to access a sql server that was accidently public due to a flaw by the net admin and killing the SA account gives you a warm feeling when you see some poor sod of a hacker trying to break in.

I'd also seriously consider having a junior work on a personal instance while running/learning scripts. Work on a production or even dev server should be monitored as it could impact production or the team.

As to the punishment, yep I agree, we've all been there. Basic rule of thumb for me is if someone tells you about it. If they try hide it, then come down like a ton of bricks but admiting a mistake is human and expected.
Click to expand...

You make a point. His machine is still pending so hopefully it will come through within the next week.
 
hypervdm said:
The -m makes it start in a kind of a management mode. It gives you god mode when connecting through sqlcmd :-)

Dont ever disable the sa account unless you are working with Express versions, you are opening yourself to a world of problems when using Agent Jobs. Rather make the password very strong. If you are getting attacks on your SQL server you need to talk to the security team. SQL servers should be isolated behind FortiGate.
Click to expand...

Hmmm, I disagree regarding the sa, never had any issues around it yet but I take quite extensive pains in the setup of the server and respective jobs/automation. 100% about being behind a firewall but you get the odd networking person who does something and suddenly it can be seen.

Also very annoying when you get someone installation a 3rd party software requiring a database and they insist on wanting the sa account and password. Sorry, will help with the setup but you can have a nice ring fenced login for your application.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X