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