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.

Answer 1

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 

Answer 2

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.