Sunday, November 04, 2007

CREATE DATABASE Permission Denied - SQL Express 2005 Problem and Solution

Today I installed SQL Express 2005 on my Windows Vista PC and a SQL Server Express 2005 instance with the name GopinathMPC\SQLExpress is created successfully.

As a first step of using the new SQL Server Express 2005 I started creating a database with the query

CREATE DATABASE TestDB

GO

Unexpectedly execution of the above query failed with the following error message

Msg 262, Level 14, State 1, Server GOPINATHM-PC\SQLEXPRESS, Line 1
CREATE DATABASE permission denied in database 'master'.

The error message indicates that I don't have enough permissions to create the database. The login which I'm using to access my Windows Vista has administrative privileges, but still I'm not granted administrative privileges on the SQL Server instance.

Looking through the documentation of SQL Server Express, I found the that

Windows Vista users that are members of the Windows Administrators group are not automatically granted permission to connect to SQL Server, and they are not automatically granted administrative privileges.

Now it is very clear that event though I'm an administrator on my Windows Vista OS I don't have administrative rights on SQL Express 2005 Server. So I need to get administrative rights.

How to Grant Administrative Rights on SQL Express 2005?

  1. Log in to Windows Vista using your administrative account
  2. Open SQL Server Surface Area Configuration Application ( Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools -->SQL Server Surface Area Configuration)
  3. Click on Add New Administrator (pointed in the image) link

CREATE DATABASE Permission Denied - SQL Express 2005 - Image 1

  1. A new window with title 'SQL Server User Provisioning on Vista' popup and displays the permissions on the left panel.
  2. Select the permission 'Member of SQL Server SysAdmin role on SQLEXPRESS' available on the left panel and add it to the right panel with the help of add button( button with > text) available in the window.

CREATE DATABASE Permission Denied - SQL Express 2005 - Image 2

  1. Click on OK button to save the changes.
  2. That's all now your Windows login has administrative privileges on SQL Server.

53 Comments:

Anonymous said...

I have the same problem but on my computer there is no button to "add new administrator" in my surface area configuration application. Thank you Vista...

Gopinath M said...

Are you logging in as administrator of your PC? If you login as administrator, then you will be able to see Add New Administrator link.

Mark said...

Great tip, helped me. Thanks!

Stuart said...

Thanks - exactly what I was looking for

Gopinath M said...

Mark and Stuart, you are welcome.

Anonymous said...

i logged in as administrator account but 'add new administrator' doenst seen. :(

gyanendra dixit said...

i have also that same problem that in my computer only one account is shown named administrator and no such button show
"add new administrator"

Gopinath M said...

Gyanendra,
The login which you used in not an administrator. That's why you are not a able to see "add new administrator" link. Please login as administrator of your PC.

Anonymous said...

thanks a lot dude

this was a great tip

Anonymous said...

hi,
i did the above things but still getting the same error. is there a way to know that the admin righta have been granted?
thanks

Aldavo said...

Hi Gopinath, I tried to do as you suggested, but it does not seem to work. I am still unable to create a new database from Visual Basic 2005 Express edition which as administrator in Vista. Any ideas?

Anonymous said...

Hi Gopinath, I tried to do this on my vista machine. but it does not seem to work. I am still unable to create databases from VB 2005 Express Edition? Any ideas?

Anonymous said...

Great help friend. Thanks a lot .

Anonymous said...

Saved my day. Thanks alot

FCIan said...

hey!!..Thanks alot reaally for your help..i've been searchin for hours for a solution and you gave a very detailed one !!..and i have an SQL Lab exam and it wasn't working so thanksss :)

redzer0 said...

Hi.. can you help me? I have problems with SQL Express 2005 that i recently installed. when i try to connect to the server i have this.. localhost\SQLEXPRESS instance but i cant find any database. how can i create or move a .mdf DB file to my SQL server.. tnx a lot

drummo154 said...

Thanks!! It really is quite simple when you see how to do it! Now I have an reason to study for my SQL Lab Exam. D'oh!

sandeep said...

thanks it really help me sandeep koduru

Anonymous said...

Cheers.

Anonymous said...

Thanks so much Gopinath. I got a quick solution to the problem I was facing

Anonymous said...

Thanks for the solution! It was perfect!

Anonymous said...

great solution..had searched high and low for solution..this is the one! thanks regs dum

Anonymous said...

thank you.,,for the solution. It really save my day ;)

Anonymous said...

Thanks so much for help! It solved my problem...

Cleiton José said...

Thank you so much, in Brazil we say "you broke a big branch"

Anonymous said...

thank u gopinath.

Anonymous said...

Thanxs buddy...gr8 help...was stuck for nearly a month...

柚子茶 said...

Thanks a lot!!!

Deep said...

Thanx Gopalnath...it was a gr8 help...

I'm able to create a database now but next problem is I want to create table in this database for which I'm getting error saying


Cannot open database "c:\VC\MyDataBaseData.mdf" requeste by the login. The login failed.


I'm using C# 2005 with 3.0 framework and SQL Express edition 2005 SP2


here is the code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{

}

private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=SSPI");
string s;
s="CREATE DATABASE MyDatabase ON PRIMARY (NAME = MyDatabase_Data, FILENAME = 'C:\\VC\\MyDatabaseData.mdf', SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON (NAME = MyDatabase_Log, FILENAME = 'C:\\VC\\MyDatabaseLog.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%) ";
conn.Open();
MessageBox.Show("open");
SqlCommand sqlcmd=new SqlCommand(s,conn);
sqlcmd.ExecuteNonQuery();
MessageBox.Show("database created");
conn.Close();
MessageBox.Show("closed");
}

private void button2_Click(object sender, EventArgs e)
{
try
{
SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;DataBase='C:\\VC\\MyDataBaseData.mdf';Integrated Security=SSPI");
string st = "CREATE TABLE Books (BookID INTEGER PRIMARY KEY IDENTITY,Title CHAR(50) NOT NULL , Author CHAR(50), PageCount INTEGER,Topic CHAR(30),Code CHAR(15))";
conn.Open();
MessageBox.Show("open");
SqlCommand scmd = new SqlCommand(st, conn);
scmd.ExecuteNonQuery();
MessageBox.Show("Table Created");
conn.Close();
MessageBox.Show("Closed");
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}

freedom said...

Thanx dude.i am very grateful grantin me access to solve my problem.

but,i need a help on quick development in web development using VB.NET bcos,i am new to web development.i am wanderin wen i will master the basic concept to business solutions.

pls do well to post any clue to my email add:freedomzoz@yahoo.com
thanx once more

JOHAN said...

I have the same problem with vb 2008 but this solution doesn't seem to work

Anonymous said...

If you don't see the button mentioned in the surface area confguration that means you need to download sp2 of sql express.

Anonymous said...

i see "Add New Administrator", but when i click on it, nothing happens...i don't see any window pop ups...

any suggestion??? plz help me !!!

Anonymous said...

i am the administrator user account type and there is no other account as administrator. I can not see the "add new Administrator" What else can i do?

camaro'blog said...

T.y. successful

varun said...

Hi Gopinath,

thanks a lot for the wonderful help.

Thanks and Regards,
Varun Gupta

Brijesh said...

Thanks a lot, that really helped !!!

bhoomi said...

perfect...thanks.......

PS said...

If you don't see "Add new administrator" then you need to download and install SQL Server 2005 Service Pack 3 :

http://www.microsoft.com/downloads/details.aspx?FamilyID=ae7387c3-348c-4faa-8ae5-949fdfbe59c4&displaylang=en

(or using windows update)

Anonymous said...

Hi
i was not able to configure admin account from surface manager. this is how it resolved:

while installating express
the option to Hide Advanced config... is checked in by default. Uncheck that and run an install. you will see an option to allow admin account to the instance.

Anonymous said...

Very helpful and to the point - sorted me out in seconds. Thanks

Fitness Nutrition said...

Clear and easy to follow. Thanks you, it would have taken me hours, maybe days to figure out.

Pc Technical Support said...

An interesting and informative post. Thanks for sharing this article.

Trevor said...

thanks lots - saved me lots of time

Trev

rose angelie said...

thank u so much...it was such a big help..

dummies said...

I had the same problem and now it's fixed thanks to the step by step detail...Thank you

stajfar said...

All U need to do is:
1.login to vista by administrator
2.right click on visual studio and run as administrator
now you can create your database without permission error!!!:D

Anonymous said...

Many thanks ...

Anonymous said...

thanks a lot for provide solutions in this blog.
keep it up.

Ashu said...

hi all..
plz help me i m facing same problem like i have not seen new administrator button...
i m using window's vista ..

what should i do..
i m facing huge problem on it ...

i done all the things which is wrote in upper comments on this page but i dont get any solution ....

Tom said...

When I add "Member of SQL Server SysAdmin role on SQLEXPRESS'" and click OK, it's not saving the change. The next time that I go b back to the 'SQL Server User Provisioning on Vista' window, there are no privleges in the right panel.

Anonymous said...

THANK YOU!!!

RockStar said...

Dear Friends,

i have installed MS SQL Server 2005 and was trying to create new database, but its not letting me create and throwing blow error.

"Either the 'RekSan\Reddy' user does not have permission to create a new object in 'REKSAN', or the object does not exist.
(Microsoft.AnalysisServices)"

your help need to solve this.