Wednesday, April 04, 2012 1:39 PM
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!
Wednesday, April 04, 2012 1:49 PM
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
- Chintak (My Blog)
- Marked As Answer by August Buck Wednesday, April 04, 2012 2:49 PM
Wednesday, April 04, 2012 2:03 PM
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
- Edited by Chirag Shah Wednesday, April 04, 2012 2:04 PM
Wednesday, April 04, 2012 2:48 PM
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!