This morning, I was playing with Microsoft SQL Server since I was asked by my senior to learn using it before going to work in next week.
I thought I had the free Express version. However, when I searched through the programs list, I found out that I actually had Microsoft SQL Server 2008 R2 in my computer. Yay~ I guess I got it for free from school. Thanks MSDNAA for helping SoC students that much!
I was very happy until I run the program. When I was about to create a database, I just found that I had no permission to do so. Oh my tian… I even forgot the password that I set that time (Wait… Did I even set a password?). I looked at the users list (/Security/Logins), I only saw BUILTIN\Users and sa. Ano… Where was the BULTIN\Administrators? I later found out that due to security issues, new SQL Server 2008 no longer had BUILTIN\Administrators in the SQL Server sysadmin fixed server role (Reference: Database Engine Configuration – Account Provisioning). Also I couldn’t access using SA account because I forgot the password that I set a long, long time ago.
Besides, I also could not create new user. That was quite disappointing.
Later, I read a thread on StackOverflow about resetting the SA password: http://stackoverflow.com/questions/4188193/how-do-you-reset-the-sa-password. Their problems were very similar to mine. Although it was about resetting the SA password, I guess if I could reset the password, I should have no problem accessing the databases as well.
Hence, I decided to try out their suggestions: Starting SQL Server in single-user mode. To do so, after closing the SQL Server Management Studio window, I launched SQL Server Configuration Manager. From there, I looked for the SQL Server instance. I then right-clicked on it go to Properties.
In the Advanced tab, I entered parameter “-m;” before existing parameters in “Startup Parameters”.
Finally, I clicked on OK and then restarted the server instance.
After that, I run SQL Server Management Studio again. Then I saw the following error message when I tried to connect with Windows Authentication.
Soon, I found that it won’t show this error message if I run SQL Server Management Studio as administrator as shown below.
Yup, now I can successfully create databases, add new users and reset passwords. =)
Now, it is time to remove the “-m” parameter.
In fact, there are online tutorials about starting SQL Server instance in single-user mode, for example http://blog.sqlauthority.com/2009/02/10/sql-server-start-sql-server-instance-in-single-user-mode/
Oh yeah, if there is a need to login SQL Server Management Studio with SA account, I can enable it in the Login Properties of the SA account (Reference: http://anujyadavcse.blogspot.com/2010/08/login-failed-for-user-sa-reason-account.html).