SQL Server 2008 Express – Fix ‘Permission Denied’ When Creating Database

sql serversql-server-2008-r2

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?

Best Answer

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!

Related Topic