Permission denied when creating database in Sql Server 2008 express

Question

Running the following (generated by Sql Server Management Studio) on Sql Server 2008 Express under Vista:

CREATE DATABASE [test] ON  PRIMARY 
( NAME = N'test', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\test.mdf' , SIZE = 5120KB , FILEGROWTH = 10%)
 LOG ON 
( NAME = N'test_log', FILENAME = N'c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\test_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)

results in

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.

The surface area tool I found references to for sql 2005 no longer exists and I can't figure out how to add local admins (of which I am one) to db admins for sql express.

As far as I'm aware this is a fairly standard install.

How do I create a new database?

score 3 · Accepted Answer · edited Dec 25 '22 at 12:42

3

The 2008 R2 installer asks you during the install to pick which user will be a sql server administrator. It does not make the local admin account a sql server sysadmin like in previous versions. If you do not know what user you made admin during the install (or the user was deleted) then you'll have to start sql server in single usermode and add your own account in as a sysadmin.

You can follow these instructions
Make sure you right-click and run sqlcmd "as Administrator" For step 5, you should add your windows account as a login and then add it to the sysadmin server role with the following two commands:

exec sp_addlogin [YOULOCALMACHINENAME\YourUsername]
exec sp_addsrvrolemember [YOULOCALMACHINENAME\YourUsername], 'sysadmin'

You should then have the privs you need to run the CREATE DATABASE statement & have it work!

edited Dec 25 '22 at 12:42

Glorfindel

1,213
4
15
22

answered Aug 20 '10 at 15:13

Nick Kavadias

10,796
7
37
47

Single user mode: * start > all programs > open sql server configuration manager > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager * under Sql Server Services, right-click the default instance "SQL Server (SQLEXPRESS)", and click stop * right-click the instance again and click properties * select the advanced tab * find "Startup Parameters" in the list and add ";-m" to the end (without the quotes), click ok, and ok for the warning for restarting * right-click the service again and click start "SQL Server started in single-user mode" in the application log – Tim Abell Aug 23 '10 at 08:20
hrmph, it ate all my linefeeds – Tim Abell Aug 23 '10 at 08:20
create login [domain\tim.abell] from windows; go; http://msdn.microsoft.com/en-us/library/ms189751.aspx – Tim Abell Aug 23 '10 at 08:37
exec sp_addlogin [domain\tim.abell], 'sysadmin'; go; (I needed to quote the username in square brackets) http://technet.microsoft.com/en-us/library/ms186320.aspx – Tim Abell Aug 23 '10 at 08:40
hrm, after all that I'm still not getting the permissions, and the user isn't showing up in the list of users or under the sysadmin role. – Tim Abell Aug 23 '10 at 08:44
that should work. unless you have problems with your domain. Try creating a local account & use that. or enabled mixed mode (you can do this in the registry) and reset the sa password with sp_password – Nick Kavadias Aug 23 '10 at 22:53
incidentally i think the username has to be in square brackets, if you want to edit your answer. – Tim Abell Aug 24 '10 at 08:56

score 0 · Answer 2 · answered Aug 20 '10 at 10:26

0

Try to login as 'sa' (with correspondent password) with sql server authentication mode.

answered Aug 20 '10 at 10:26

Sergey

2,121
15
14

I would like to stick to integrated auth. – Tim Abell Aug 20 '10 at 10:48
Log in with the sa account, as sergey mentioned, and create your additional accounts. From that point you can start using integrated authentication. – Coding Gorilla Aug 20 '10 at 14:43
express has sa disabled by default – Nick Kavadias Aug 20 '10 at 15:08

score 0 · Answer 3 · answered Aug 20 '10 at 14:23

0

Have you tried using OSQL command line tool, and logging in using integrated authentication by using the -E switch. You should not use the -U and -P (username/password) swtiches.

Make sure you are logged in as an Administrator. From OSQL you could then execute your creation scripts.

answered Aug 20 '10 at 14:23

Thies

111
3

r2 doesn't add the built-in admin as a sql server sysadmin, so it wont work ;-) – Nick Kavadias Aug 20 '10 at 15:24

score 0 · Answer 4 · edited Apr 29 '13 at 23:38

0

Try this, it Worked:

exec sp_addlogin [YOULOCALMACHINENAME\YourUsername]
exec sp_addsrvrolemember [YOULOCALMACHINENAME\YourUsername], 'sysadmin'

edited Apr 29 '13 at 23:38

slm

7,615
16
56
76

answered Apr 29 '13 at 22:08

christian

1

Permission denied when creating database in Sql Server 2008 express

4 Answers4

Linked