No Permission to Create Database in SQL Server 2008 R2

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.

CREATE DATABASE Permission Denied

CREATE DATABASE Permission Denied

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.

Step 1: Right-click Server Instance, Select Properties

Step 1: Right-click Server Instance, Select Properties

In the Advanced tab, I entered parameter “-m;” before existing parameters in “Startup Parameters”.

Step 2: Add -m as Parameter

Step 2: Add -m as Parameter

Finally, I clicked on OK and then restarted the server instance.

Step 3: Restart Server Instance

Step 3: Restart 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.

Error: Only One Admin Can Connect At One Time

Error: Only One Admin Can Connect At One Time

Soon, I found that it won’t show this error message if I run SQL Server Management Studio as administrator as shown below.

Run SQL Server Management Studio as Administrator

Run SQL Server Management Studio as Administrator

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).

Enable SA Login

Enable SA Login

Advertisements

One thought on “No Permission to Create Database in SQL Server 2008 R2

  1. Pingback: No permission to create, edit, reset SQL accounts | ATS Cloud

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s