Forgot SA password, would like to get in using Windows Authentication

Question

Someone installed SQL Server but forgot the SA password. I have no idea what other sysadmin logins might have been setup. I am hoping that mixed mode was chosen for the logins so that we can also use Window logins. How can I check that it is running on mixed mode, rather than just SQL Server login mode?

Let's assume that it is running on mixed mode so that Windows logins are accepted. I added my Window login to the Administrator group and tried logging into SQL Server but that didn't work. The error was simply "Login failed for user <Domain\Login>. Would this be because SQL Server is accepting only SQL Server Authentication or because my Windows login is not in the right group?

Answer 1

Hi,

Is this SQL 2008? IF so there is only Windows auth or Windows and SQL auth modes so you should be ok. By default however the administrators group is *not* granted access to SQL. You can work around this by booting into single user mode however if you don't/can't restart SQL there is a work around which Argenis blogged here.

http://sqlblog.com/blogs/argenis_fernandez/archive/2011/07/10/think-your-windows-administrators-don-t-have-access-to-sql-server-2008-by-default-think-again.aspx

---

Sean Massey | Consultant, iUNITE

Feel free to contact me through My Blog, Twitter or Hire Me.
Please click the Mark as Answer button if a post solves your problem!

Answer 2

One more http://dimantdatabasesolutions.blogspot.com/2010/01/think-before-unchecking-sysadmin-rights.html

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

And the link to official documentation Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 4

Turns out Windows Authentication was on and Administrator login had sysadmin access so I was able to get in that way.