none
SQL Express 2008 R2 - CREATE DATABASE permission denied in database 'master'

    Question

  • I have created a setup application with SQLEXpress 2008 as a prerequisite. On test SQLExpress installs OK

    after the application is installed it must create and install a database using a mixture of SMO and Scripting. I used SMO to add the current user (who is a Windows Administrator) as a Login to the SQL Express server.

    Dim l As Login = New Login(srv, My.User.Name)
                    l.LoginType = LoginType.WindowsUser
                    l.AddToRole("sysadmin")
                    l.DefaultDatabase = "MASTER"
                    l.Create()

    It fails on srv.CreateDatabase with error message "permission denied in database MASTER"

    this thread talks about converting the server to single user mode before adding a login

    http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/86daf5f5-d333-40b1-ae6f-0da052a96600

    how is it possible to scrip this? there is no documentation i can find. 

    the database must be installed in the ProgramData folder. Is there a permissions issue with this. Do I need to assign permissions to this folder somehow?

    bear in mind this will be a remote install and the user expects it work flawlessly.  No manual interventions

     

     

     

     

    Sunday, November 14, 2010 3:01 PM

Answers

  • I think I figured it out.  I'm now having a problem importing data (more on that later), but at least I can create a database.  Here's what I did:

     

    1.  shut down SQL Server from services

    2.  open cmd window (as admin) and run single-user mode as local admin with this command:

    "c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

    3.  open another cmd window (as admin)

    4.  open sqlcmd:

    sqlcmd -S .\SQLEXPRESS

    Now add the sysadmin user:

    a.  sp_addsrvrolemember 'domain\user', 'sysadmin'

    b.  GO

    5.  now Ctrl+C the single-user mode from the first cmd window to kill SQL Server.  Now restart it from services the normal way.  Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

     

    Before this, I was getting the error "Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)."  What the error utterly fails to tell you is that any connection to single-user mode qualifies as an administrator connection.  In effect, simply opening Management Studio in single-user mode was causing an administrator to connect.  The only way to do anything in single-user mode (it appears) is to do it thru sqlcmd.

     

    So now I'm a sysadmin on my own machine!  Amazing, isn't it?  Unfortunately, now I'm having problems importing data.  The error I get is: "The value could not be converted because of a potential loss of data."  Again, a completely bewildering, unhelpful, and potentially misleading error message.  But like I said, I've made progress.  However, I fully expect to fruitlessly google this new error for at least three hours tomorrow.

    Monday, November 15, 2010 10:48 PM

All replies

  • I believe during setup you didnot add acccount as SysAdmin. So you have many choices:
    1. Add Logged-In Account as SysAdmin during installation.
    2. Install SQL in Mixed Mode and Use sa account for settingup the database.

    Most likely cause of your login token is not sent to properly due to UAC. Run your setup program with elevated rights.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Sunday, November 14, 2010 3:38 PM
    Moderator
  • You need to use SQL Server SMO to add a login and then add that login as dbcreator fixed server role

    code snippet is available in the following links

    http://sysarcana.com/2010/07/12/managing-sql-server-users-with-c-via-smo/

    http://social.msdn.microsoft.com/Forums/en/sqlsmoanddmo/thread/e49db1d5-db22-4a2d-a333-9350f07400b8

    Sunday, November 14, 2010 3:42 PM
  • 1. Run your setup program with elevated rights.

    doesn't the setup program impersonate the windows user permissions. If the windows user is an administrator isn't this as considered elevated rights

    2. regarding mixed mode login I'm not really keen on it but everything I do gets this message  

    {"User does not have permission to perform this action."}

    Sunday, November 14, 2010 4:20 PM
  • if i connect to the sqlexpress instance  (local)\SQLEXPRESSS  using SQL Management Studio and try to modify the Windows User Login I get the error message:

    "User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)"

     

    how is this possible. it seems to be at an impasse. can't go forward or backward

     

    Sunday, November 14, 2010 10:53 PM
  • if i connect to the sqlexpress instance  (local)\SQLEXPRESSS  using SQL Management Studio and try to modify the Windows User Login I get the error message:
    "User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)"
    how is this possible. it seems to be at an impasse. can't go forward or backward 

    Now try launching Managenment Studio by right clicking on shortcut and choosing "Run As Adminitstartor".
    If above works then you are running on Windows 2008 family OS (Vista, 2k8 or Win7) which has UAC feature.

    If above does not work then you really don't have permission then follow below. 

    Troubleshooting: Connecting to SQL Server When System Administrators Are Locked Out

    Start the instance of SQL Server in single-user mode by using either the -m or -f options. Any member of the computer's local Administrators group can then connect to the instance of SQL Server as a member of the sysadmin fixed server role.

    http://msdn.microsoft.com/en-us/library/dd207004.aspx




    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, November 15, 2010 1:47 AM
    Moderator
  • 1. Run your setup program with elevated rights.

    doesn't the setup program impersonate the windows user permissions. If the windows user is an administrator isn't this as considered elevated rights

    2. regarding mixed mode login I'm not really keen on it but everything I do gets this message  

    {"User does not have permission to perform this action."}


    Well, thats UAC feature. Logged in user (even admin) will not be able to perform such tasks unless explicitly specified. Normally virus works with same logic. They impersonate you as admin and perform all sort of tasks to damage your system.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, November 15, 2010 1:50 AM
    Moderator
  • Balmukund

    I was locked out and was able to reconnect to SQL express by running in single user mode.  The Windows login did not have the sysadmin role, and I deleted it

    If try to add the login programmatically like below, I get the error message: "User does not have permission to perform this action". So the application needs to run with elevated permissions but how do you do this?

     

    If Not srv.Logins.Contains(My.User.Name) Then
        Dim l As Login = New Login(srv, My.User.Name)
        l.LoginType = LoginType.WindowsUser
        l.DefaultDatabase = "MASTER"
        l.AddToRole("sysadmin")
        l.Create()
       End If

     

    Monday, November 15, 2010 10:13 AM
  • is that a exe? or script which runs from command prompt?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, November 15, 2010 10:33 AM
    Moderator
  • Balmukund

    its an exe.  Its part of a custom desktop application. The idea is that you should be able to install SQLExpress and then create a database, and then run that application as normal

     anyway I keep getting locked out in SQL Server Management Studio as well.  Perhaps creating a permission denied exception causes this. 

    I think the security approach here is rendering this database unusable from a pratical point of view. if so its wasted a serious amount of my time

     Does anybody know any alternatives: Please

     

    Monday, November 15, 2010 1:15 PM
  • I'm having the same problem.  Can't create a database to save my life.  Tried running Management Studio as administrator and got the same error.  Also tried about 20 other suggestions gleaned from about two hours' worth of googling.  Unfortunately, a lot of the suggestions pertain to SQL 2005, none of which work with 2008.  There used to be something in 2005 called Surface Area Configuration, which apparently would fix all this.  Naturally, that option was removed for 2008, but there does exist in Management Studio a way to get to this via Facets > General > Surface Area Config.  Well, it apparently doesn't do the same things that it did in 2005, so it's useless.  What a surprise.

     

    Tried changing the SQL Server service to run as a local admin (instead of Network Service).  Doesn't work.  Tried adding/changing logins from inside Management Studio.  Doesn't work because it tells me I don't have permission to do that (running Studio as admin, mind you).  Nothing works.  Absolutely nothing works. The server is so secure that no one can use it.  It reminds me of when I used to run Linux back in the 90s.  Can't do A because B doesn't work.  OK, figure out how to fix B.  Oops, can't fix B until C is fixed.  OK, fix C.  Nope, D must first be fixed!  Next step: give up.

     

    I'm in the process of learning ASP.NET and SQL Server.  Things need to be simple so I can just learn this stuff.  It is extremely frustrating being impeded by security problems that are keeping me from doing my work.  There should be an option to run SQL Server Express wide open without this hassle.

     

    I see that this is a common error and many people are having this problem.  Any suggestions would be most helpful.  Thank you.

    Monday, November 15, 2010 5:25 PM
  • I think I figured it out.  I'm now having a problem importing data (more on that later), but at least I can create a database.  Here's what I did:

     

    1.  shut down SQL Server from services

    2.  open cmd window (as admin) and run single-user mode as local admin with this command:

    "c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

    3.  open another cmd window (as admin)

    4.  open sqlcmd:

    sqlcmd -S .\SQLEXPRESS

    Now add the sysadmin user:

    a.  sp_addsrvrolemember 'domain\user', 'sysadmin'

    b.  GO

    5.  now Ctrl+C the single-user mode from the first cmd window to kill SQL Server.  Now restart it from services the normal way.  Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

     

    Before this, I was getting the error "Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)."  What the error utterly fails to tell you is that any connection to single-user mode qualifies as an administrator connection.  In effect, simply opening Management Studio in single-user mode was causing an administrator to connect.  The only way to do anything in single-user mode (it appears) is to do it thru sqlcmd.

     

    So now I'm a sysadmin on my own machine!  Amazing, isn't it?  Unfortunately, now I'm having problems importing data.  The error I get is: "The value could not be converted because of a potential loss of data."  Again, a completely bewildering, unhelpful, and potentially misleading error message.  But like I said, I've made progress.  However, I fully expect to fruitlessly google this new error for at least three hours tomorrow.

    Monday, November 15, 2010 10:48 PM
  • "I'm in the process of learning ASP.NET and SQL Server.  Things need to be simple so I can just learn this stuff.  "

    sql server and visual studio is a highly productive and easy to use development environment.

    its just that the security model is unpredictable and hopeless for certain kinds of deployment scenarios. What you gain in development efficiency and time, you lose it here. This should also be included in any cost benefit analysis

    I don't think there is an easy answer so perhaps the mods can consider this closed.  

    Tuesday, November 16, 2010 10:35 AM
  • I did all this and I received the following message :

    Msg 2812, Level 16, State 62, Server Sanziana-PC\SQLEXPRESS, Line 1

    Could not find stored procesure 'sp_addsvrolemember'

     

    What should I do next ?

     

    Thanks.

    Saturday, November 19, 2011 9:51 PM
  • Your suggestion worked! Thanks!

    It's unbelievable that so much effort is required for a windows admin user to create a DB in the SQL server he just himself installed! Doesn't seem rational to me.

    Thursday, January 05, 2012 4:23 PM
  • Thanks for this info! Probably saved me from pulling out the last few remaining hairs on my head!

    Just for curiosity what was the problem in the end with the data import? I haven't attempted to import data yet just want to have that solution on hand should the need arrise.

    Thanks again

    mrrcomp


    meir r.

    Thursday, November 08, 2012 7:47 AM
  • Great work Blipsalt. Excellent stuff..astonishing stuff from Microsoft as admin user needs to do so much for permission to create db in Sql server 2008.

    Thanks

    Ankur Bhutani


    Tuesday, November 27, 2012 5:19 PM
  • Thank you so much, Blipsalt.  You made it so simple and straight forward.  Saved my night sleep...:)
    Thursday, April 04, 2013 5:15 AM
  • i not able to write the last command ..

    sqlservr.exe" -m -s SQLEXPRESS 

    and in sqlcmd i was not able to write anything at all 

    i have the same problem of creating database denied by master database ..

    kindly guide me how can i fix it ..

    Thursday, October 10, 2013 9:42 AM
  • Thanks. It Solved My problem
    Monday, May 05, 2014 10:13 AM
  • Thanks. It Solved My problem
    Monday, May 05, 2014 10:13 AM