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?
- Log in to Windows Vista using your administrative account
- Open SQL Server Surface Area Configuration Application ( Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools -->SQL Server Surface Area Configuration)
- Click on Add New Administrator (pointed in the image) link
- A new window with title 'SQL Server User Provisioning on Vista' popup and displays the permissions on the left panel.
- 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.
- Click on OK button to save the changes.
- That's all now your Windows login has administrative privileges on SQL Server.
53 Comments:
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...
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.
Great tip, helped me. Thanks!
Thanks - exactly what I was looking for
Mark and Stuart, you are welcome.
i logged in as administrator account but 'add new administrator' doenst seen. :(
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"
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.
thanks a lot dude
this was a great tip
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
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?
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?
Great help friend. Thanks a lot .
Saved my day. Thanks alot
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 :)
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
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!
thanks it really help me sandeep koduru
Cheers.
Thanks so much Gopinath. I got a quick solution to the problem I was facing
Thanks for the solution! It was perfect!
great solution..had searched high and low for solution..this is the one! thanks regs dum
thank you.,,for the solution. It really save my day ;)
Thanks so much for help! It solved my problem...
Thank you so much, in Brazil we say "you broke a big branch"
thank u gopinath.
Thanxs buddy...gr8 help...was stuck for nearly a month...
Thanks a lot!!!
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);
}
}
}
}
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
I have the same problem with vb 2008 but this solution doesn't seem to work
If you don't see the button mentioned in the surface area confguration that means you need to download sp2 of sql express.
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 !!!
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?
T.y. successful
Hi Gopinath,
thanks a lot for the wonderful help.
Thanks and Regards,
Varun Gupta
Thanks a lot, that really helped !!!
perfect...thanks.......
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)
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.
Very helpful and to the point - sorted me out in seconds. Thanks
Clear and easy to follow. Thanks you, it would have taken me hours, maybe days to figure out.
An interesting and informative post. Thanks for sharing this article.
thanks lots - saved me lots of time
Trev
thank u so much...it was such a big help..
I had the same problem and now it's fixed thanks to the step by step detail...Thank you
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
Many thanks ...
thanks a lot for provide solutions in this blog.
keep it up.
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 ....
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.
THANK YOU!!!
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.
Post a Comment