Forgot sysadmin user password

Question

Forgot sysadmin user password

IT researcher

SSCertifiable

Points: 7600
More actions
April 25, 2013 at 11:31 pm

#293041

In a SQL server instance I have only SQL logins with sysadmin permission. Windows authentication has only public permission. So in case if I forget password of the SQL login having sysadmin then how can I recover it or What steps I should take?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Vegard Hagen Hall of Fame Points: 3078 More actions · Answer 1

Well, first of all, best practice is to use Windows authentication, preferably in combination with AD groups, so that everyone logs in with their own personal account but you don't need to administer all those personal accounts on each SQL Server. For instance, you can create "SQL DBA" group in AD and add all members of your SQL DBA team to this group. Then you only have to add this group to each SQL Server and grant sysadmin server role and voila! All DBAs now have sysadmin on all SQL servers - and if people leave or new guys join, you just have to add or remove members in the AD group. That's one place you need to make a change instead of everywhere you have a SQL Server. Well... One place for each domain, that is. 😉

Now, to answer your question, there IS a way for a Windows admin to take control of a local SQL Server even if he's not a SQL admin. Basically, what you need to do is to start the SQL server in Single User mode, also known as maintenance mode - startup option "-m". When SQL is running in single user mode, it is possible for a Windows admin to connect and perform administrative tasks even if he doesn't have the appropriate SQL Server permissions. So, you can start the server in single user, connect to it using windows authentication and either add a new login with sysadmin role or grant sysadmin to an exisiting login.

Pinal Dave (SQL Authority) has written a blog post about this which can be found here:

http://blog.sqlauthority.com/2009/08/04/sql-server-forgot-the-password-of-username-sa/[/url]

Kind regards,

Vegard Hagen
Norwegian DBA, occasional blogger and generally a nice guy who believes the world is big enough for all of us.
@vegard_hagen on Twitter
Blog: Vegards corner (No actual SQL stuff here - havent found my niche yet. Maybe some day...)

It is better to light a candle than to curse the darkness. (Chinese proverb)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Vegard Hagen (4/26/2013)
Well, first of all, best practice is to use Windows authentication, preferably in combination with AD groups, so that everyone logs in with their own personal account but you don't need to administer all those personal accounts on each SQL Server. For instance, you can create "SQL DBA" group in AD and add all members of your SQL DBA team to this group. Then you only have to add this group to each SQL Server and grant sysadmin server role and voila! All DBAs now have sysadmin on all SQL servers - and if people leave or new guys join, you just have to add or remove members in the AD group. That's one place you need to make a change instead of everywhere you have a SQL Server. Well... One place for each domain, that is. 😉

I'd like to echo this approach. It is exactly what we do so everyone's connecting with Windows authentication. I've found it is far better to use this approach than for everyone to have their own SQL login. It makes tracking thing down much easier and it makes administration easier. If anyone ever leaves the company and their Windows account is locked, they can't use their SQL login either. Sometimes DBAs aren't told of someone leaving in a timely fashion, but Domain Administrators always are! Just another level of security.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQL Show SSCertifiable Points: 6143 More actions · Answer 3

SQL Show

SSCertifiable

Points: 6143

April 26, 2013 at 8:39 am

#1610414

have you tried Psexec.exe?

david.alcock SSCertifiable Points: 6021 More actions · Answer 4

Stop SQL service

Start SQL in single user mode via command prompt with -m, essentially this allows any member of the local admins group to connect as a member of the sysadmin role.

Create new login

Restart service.

Apologies to Vegard Hagen and above, didnt see it was already posted as I was rushing!

'Only he who wanders finds new paths'

Lowell SSC Guru Points: 323518 More actions · Answer 5

also check out this neat trick:http://sev17.com/2011/10/22/gaining-sql-server-sysadmin-access/

basically it's a model for a SQLCMD script which would add a new sysadmin user, because it is able to run as the built in user "NT AUTHORITY\SYSTEM" (because you are a sysadmin on the local box, and can run as that user)

using that, you do not have to stop your system and restart it in single user mode...which might be a plus for some people.

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!