Unable to create database - sql express and xp pro (XP user is local admin)

Question

Default install of sql server express, have done nothing with it really.  my XP user is local admin. just want to create a new database to learn in (I'm actually learning c# and trying to code against a database...just want to make a 'scratch' database where it doesn't matter if I blow it up)

...gives this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for Database 'asdf'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1750.9+((dac_inplace_upgrade).101209-1051+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476
CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

I've read online a bit but seems most things are over my head...I'm brand new to sql server, don't want anything fancy...One thing I did find was to run this sql:

SELECT * FROM fn_my_permissions(NULL, 'SERVER')

entity_name    subentity_name    permission_name
server                                           CONNECT SQL
server                                           VIEW ANY DATABASE

this one didn't return anything:

SELECT * FROM fn_my_permissions(NULL, 'SERVER') WHERE permission_name IN('ALTER ANY LOGIN','ALTER ANY CREDENTIAL')

and I also tried (from some random forum suggestion...):

ALTER LOGIN sa with PASSWORD='password'

Msg 15151, Level 16, State 1, Line 3
Cannot alter the login 'sa', because it does not exist or you do not have permission.

Really appreciate any suggestions - I'm not one to post on forums normally...just tired of banging my head against this!

Answer 1

Hi,

So, looks like you need not know SA password and your windows user is not part of SYSADMIN as you have installed sql 2008 or sql 2008 R2 express edition.

If this is the case then you can make one known sysadmin user by steps mentioned below

http://beyondrelational.com/modules/2/blogs/115/posts/11143/how-to-access-a-newly-installed-sql-server-2008-r2-instance-if-you-do-not-know-the-login-and-passwor.aspx

---

- Chintak (My Blog)

Answer 2

In addition,

Starting with SQL 2008 windows admin are not given admin rights in SQL server (unless explicityly granted during setup or at a later stage)

You may want to follow step outlined here

Connect to SQL Server When System Administrators Are Locked Out

http://msdn.microsoft.com/en-us/library/dd207004.aspx

Answer 3

Thank you for the guidance, got it working!

For whatever reason, when I did -mSqlcmd with the sqlsrvr command it didn't work...when I just said -m I was able to connect *shrug*.

Very much appreciate your help!