Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to reset SA password Expand / Collapse
Author
Message
Posted Tuesday, April 30, 2013 2:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:37 PM
Points: 19, Visits: 115
I have couple SQL servers that nobody knows the SA password and nobody has sys_admin right to reset it. There is no BUILTIN either. How could I reset the SA password?
Post #1448240
Posted Tuesday, April 30, 2013 2:57 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
As far as I know, you need at least, to be Local Admin or part of Windows Admin Group on the machine running SQL.

You need to stop the instance, and start MS-SQL on single user mode. Once you start on single user mode, connect using sqlcmd and add yourself or whoever needs it, to the sysadmin server role, this way:

EXEC sp_addsrvrolemember 'CONTOSO\Joe', 'sysadmin';
GO

Now, all those actions are really intrusive: stop sql, star sql, override permissions, etc. So be sure you have permissions for that. Any attempt to do this without proper authorization or informing your bosses or right department, may cause you serious problems, in some cases, loss your job.
Post #1448246
Posted Tuesday, April 30, 2013 3:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 24, 2014 1:37 PM
Points: 19, Visits: 115
I have tried this. The problem is the user I login with (my domain account) when I use command line does not have right to grant sys_admin right. I am already in the administrator group on the server. I think the problem is the BUILTIN is not in SQL.
Post #1448250
Posted Tuesday, April 30, 2013 3:06 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:24 AM
Points: 373, Visits: 1,235
will75 (4/30/2013)
I have tried this. The problem is the user I login with (my domain account) when I use command line does not have right to grant sys_admin right. I am already in the administrator group on the server. I think the problem is the BUILTIN is not in SQL.


If you are part of Windows Admin Group on that machine, and you start MS-SQL on single user mode, you should be able to reset sa:

Disaster Recovery: What to do when the SA account password is lost in SQL Server 2005:
http://blogs.msdn.com/b/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

Now, above process is for SQL2005, but I do believe still applies for SQL2008.
Post #1448253
Posted Tuesday, April 30, 2013 3:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:24 AM
Points: 23,019, Visits: 31,539
You have followed the information in the following article: http://msdn.microsoft.com/en-us/library/ms188236(v=sql.100).aspx?


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1448254
Posted Friday, May 3, 2013 7:43 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:17 AM
Points: 172, Visits: 408
There are some password reset utilities out there - Advanced Password Recovery - which can reset things. Not sure the costs - but worth a look as a last resort.


________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.
Post #1449202
Posted Friday, May 3, 2013 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 9:17 AM
Points: 172, Visits: 408
You always can login using windows authentication and reset the password in SQL management studio. if your SQL installs are installed in mixed mode with both SQL and Windows authentication.



________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP

Please don't trust me, test the solutions I give you before using them.
Post #1449205
Posted Friday, May 3, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 12,881, Visits: 31,821

some links i have saved:
adding a sysadmin back toa server:
http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-if-you-do-not-know-the-login-and-passwor.aspx

http://sev17.com/2011/10/22/gaining-sql-server-sysadmin-access/


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1449208
Posted Monday, May 6, 2013 5:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 15, 2013 6:32 AM
Points: 34, Visits: 70
If you already in administrator group then just login using windows authentication, Create new user with admin rights and login through that login.
Using new login u also can chaneg password of already existing SA user.
Post #1449680
Posted Tuesday, November 19, 2013 10:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 4, 2014 3:22 AM
Points: 6, Visits: 9
Ask you SA (system administrator) to reset the password.
Thanks.
Post #1515887
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse