SQL Server Express CREATE DATABASE permission denied in database 'master'

Question

After I change the option as UserInstance="False", then the error starts to happen.

Because I want to use full-text search, the option change is required. BUT, it stopped to work. Is there any way to make it work again?

I'm running Application Pool as Network Service with full control.

Zeb

Answer 1

A solution is presented here not exactly for your problem but exactly for the given error.

1. Start --> All Programs --> Microsoft SQL Server 2005 --> Configuration Tools --> SQL Server Surface Area Configuration

2. Add New Administrator

3. Select 'Member of SQL Server SysAdmin role on SQLEXPRESS' and add it to right box.

4. Click Ok.

Alexaalexander

For me, with SQL 2012 on windows server 2012, i only had to search for sql management studio, then right click and "Run as administrator." Then this error went away when i created a DB. This is the same as @Patel Ravi's answer below, but there's a lot of noise between this answer and Patel's which may keep you from finding it.... – Brasilein

Geovani's answer helped me with SQL 2008. 2008 appears to not have the SQL Server Surface tool. I did not see sql management studio on my computer. – Moa

Like @jaminto, running SQL Server Management Studio as Administrator (right-click, Run as Administrator) allowed me to create the database. WOOT – Spinney

Answer 2

1. Download the script from this Microsoft Site
2. Run it as Administrator
3. Follow the instructions and your set.

UPDATE 9/3/2014

The Microsoft URL above is no longer valid, someone thou took the time to save it to GitHubGist and the link is as follows https://gist.github.com/wadewegner/1677788

UPDATE 11/1/2021

Below is the entire script, don't recall being able to do this back in 2014, I guess this one of the perks of 2021.

    @echo off
    rem
    rem ****************************************************************************
    rem
    rem    Copyright (c) Microsoft Corporation. All rights reserved.
    rem    This code is licensed under the Microsoft Public License.
    rem    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
    rem    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
    rem    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
    rem    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
    rem
    rem ****************************************************************************
    rem
    rem CMD script to add a user to the SQL Server sysadmin role
    rem
    rem Input:  %1 specifies the instance name to be modified. Defaults to SQLEXPRESS.
    rem         %2 specifies the principal identity to be added (in the form "<domain>\<user>").
    rem            If omitted, the script will request elevation and add the current user (pre-elevation) to the sysadmin role.
    rem            If provided explicitly, the script is assumed to be running elevated already.
    rem
    rem Method: 1) restart the SQL service with the '-m' option, which allows a single connection from a box admin
    rem            (the box admin is temporarily added to the sysadmin role with this start option)
    rem         2) connect to the SQL instance and add the user to the sysadmin role
    rem         3) restart the SQL service for normal connections
    rem
    rem Output: Messages indicating success/failure.
    rem         Note that if elevation is done by this script, a new command process window is created: the output of this
    rem         window is not directly accessible to the caller.
    rem
    rem
    setlocal
    set sqlresult=N/A
    if .%1 == . (set sqlinstance=SQLEXPRESS) else (set sqlinstance=%1)
    if /I %sqlinstance% == MSSQLSERVER (set sqlservice=MSSQLSERVER) else (set sqlservice=MSSQL$%sqlinstance%)
    if .%2 == . (set sqllogin="%USERDOMAIN%\%USERNAME%") else (set sqllogin=%2)
    rem remove enclosing quotes
    for %%i in (%sqllogin%) do set sqllogin=%%~i
    @echo Adding '%sqllogin%' to the 'sysadmin' role on SQL Server instance '%sqlinstance%'.
    @echo Verify the '%sqlservice%' service exists ...
    set srvstate=0
    for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
    if .%srvstate% == .0 goto existerror
    rem
    rem elevate if <domain/user> was defaulted
    rem
    if NOT .%2 == . goto continue
    echo new ActiveXObject("Shell.Application").ShellExecute("cmd.exe", "/D /Q /C pushd \""+WScript.Arguments(0)+"\" & \""+WScript.Arguments(1)+"\" %sqlinstance% \""+WScript.Arguments(2)+"\"", "", "runas"); >"%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
    call "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js" "%cd%" %0 "%sqllogin%"
    del "%TEMP%\addsysadmin{7FC2CAE2-2E9E-47a0-ADE5-C43582022EA8}.js"
    goto :EOF
    :continue
    rem
    rem determine if the SQL service is running
    rem
    set srvstarted=0
    set srvstate=0
    for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
    if .%srvstate% == .0 goto queryerror
    rem
    rem if required, stop the SQL service
    rem
    if .%srvstate% == .1 goto startm
    set srvstarted=1
    @echo Stop the '%sqlservice%' service ...
    net stop %sqlservice%
    if errorlevel 1 goto stoperror
    :startm
    rem
    rem start the SQL service with the '-m' option (single admin connection) and wait until its STATE is '4' (STARTED)
    rem also use trace flags as follows:
    rem     3659 - log all errors to errorlog
    rem     4010 - enable shared memory only (lpc:)
    rem     4022 - do not start autoprocs
    rem
    @echo Start the '%sqlservice%' service in maintenance mode ...
    sc start %sqlservice% -m -T3659 -T4010 -T4022 >nul
    if errorlevel 1 goto startmerror
    :checkstate1
    set srvstate=0
    for /F "usebackq tokens=1,3" %%i in (`sc query %sqlservice%`) do if .%%i == .STATE set srvstate=%%j
    if .%srvstate% == .0 goto queryerror
    if .%srvstate% == .1 goto startmerror
    if NOT .%srvstate% == .4 goto checkstate1
    rem
    rem add the specified user to the sysadmin role
    rem access tempdb to avoid a misleading shutdown error
    rem
    @echo Add '%sqllogin%' to the 'sysadmin' role ...
    for /F "usebackq tokens=1,3" %%i in (`sqlcmd -S np:\\.\pipe\SQLLocal\%sqlinstance% -E -Q "create table #foo (bar int); declare @rc int; execute @rc = sp_addsrvrolemember '$(sqllogin)', 'sysadmin'; print 'RETURN_CODE : '+CAST(@rc as char)"`) do if .%%i == .RETURN_CODE set sqlresult=%%j
    rem
    rem stop the SQL service
    rem
    @echo Stop the '%sqlservice%' service ...
    net stop %sqlservice%
    if errorlevel 1 goto stoperror
    if .%srvstarted% == .0 goto exit
    rem
    rem start the SQL service for normal connections
    rem
    net start %sqlservice%
    if errorlevel 1 goto starterror
    goto exit
    rem
    rem handle unexpected errors
    rem
    :existerror
    sc query %sqlservice%
    @echo '%sqlservice%' service is invalid
    goto exit
    :queryerror
    @echo 'sc query %sqlservice%' failed
    goto exit
    :stoperror
    @echo 'net stop %sqlservice%' failed
    goto exit
    :startmerror
    @echo 'sc start %sqlservice% -m' failed
    goto exit
    :starterror
    @echo 'net start %sqlservice%' failed
    goto exit
    :exit
    if .%sqlresult% == .0 (@echo '%sqllogin%' was successfully added to the 'sysadmin' role.) else (@echo '%sqllogin%' was NOT added to the 'sysadmin' role: SQL return code is %sqlresult%.)
    endlocal

Devotional

Unfortunately the link is no longer valid, the MS archive has been retired. – Tartrazine

Good thing someone took the time to store/extract it as a Gist gist.github.com/wadewegner/1677788 – Devotional

Thank you Geovani! Saved my bacon. Could you please add the gist link to your answer? I thought for a moment that your comment was from a random helpful user. – Moa

It Worked..Thanks – Elegiac

Still works. I am using Windows 10. MS SQL Server 2014. – Christoffer

Answer 3

Solution for Microsoft SQL Server, Error: 262

Click on Start -> Click in Microsoft SQL Server 2005 Now right click on SQL Server Management Studio Click on Run as administrator

Asel

Answer 4

i have same problem, and i try this:

1. log In As Administrator on your PC
2. log In SQL Server Management Studio as "Windows Aunthication"
3. Click Security > Login > choose your login where you want to create DB > right click then Properties > click server roles > then checklist 'dbcreator'.
4. Close and log in back with your login account.

This worked for me, and hope will help you.

*sorry for my bad english

Garfield

You probably only need dbcreator, not sysadmin – Unerring

Answer 5

If you got the same error in Sql server 2008 management studio than below link will resolve this error after so much effort i found this link: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/76fc84f9-437c-4e71-ba3d-3c9ae794a7c4/

Legrand

Answer 6

What login are you connecting to SQL Server as? You need to connect with a login that has sufficient privileges to create a database. Network Service is probably not good enough, unless you go into SQL Server and add them as a login with sufficient rights.

Mejia

Answer 7

I know, it is an old question, but no solution worked for me. Here is what I did:

USE master 
GO 
GRANT CREATE TABLE TO PUBLIC

Repeat this with every permission you need, for example GRANT CREATE DATABASE TO PUBLIC, ... You must have the server role "public" (yes, I am captain obvious).

Note 1: GRANT ALL TO PUBLIC is deprecated, does not work anymore in 2017.

Note 2: I tried to build an msi installer using the wix toolset. This toolset calls a powershell file, wich creates the databases, ... Just to give you some background information :)

Ardisardisj

Perfect, worked for me too. Just to add that this seems to be specifically an SQL Express issue and solution. An identical database, same script, had no issues in another edition. – Breccia

Answer 8

After I performed the following instructions in SSMS, everything works fine:

create login [IIS APPPOOL\MyWebAPP] from windows;

exec sp_addsrvrolemember N'IIS APPPOOL\MyWebAPP', sysadmin

Unbidden

is it dangerous? – Wileywilfong

Answer 9

Addition to @Kho dir answer.

This also works if you are not able to create a database with the windows user. you just need to login with the SQL Server Authentication then repeat the process mentioned by @Kho dir.

Usm

Answer 10

For SQL server 2012,

1. First, log in to the SQL server as an administrator and go to Security tab

2. Then move into Server Roles and double click on sysadmin role

3. Now add user which you want to give permission to create Database by clicking Add button

4. Click OK button and now run the query

Hope this will help for someone

Relique

Answer 11

1. Log into on your Server/PC with administrator account

2. Log into SQL Server Management Studio as "Windows Authentication"

3. Click Security -> Logins -> choose your -> right click then choose Properties or Double click -> click Server Roles -> then checklist 'dbcreator' and 'sysadmin' then click the OK button.

4. Refresh your databases.

Now, you can create new database.

Southwester

Answer 12

That's because you have selected your Master table on the table drop down Table Selected

Select the table you want to use and proceed executing your query

Maggie