none
CREATE DATABASE permission denied in database 'master'

    Question

  • Hi,

    I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.

    I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights.  This means that I installed VS 2005 using this window user "Glen"

    I created another windows user "TestUser" (Limited account) in the same physical PC.

    I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']

    At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.

    I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.

    SO HERE ARE MY QUESTIONS :

    1.  Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?

    2.  Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)

    Thanks in advance for all you help.

     

     

     

     

    Wednesday, December 20, 2006 8:09 AM

Answers

  • That's right. You can also set that in the Services applet of the Control Panel.

    The application database is the only one that needs a user account, in addition to a server login. If you check that site on InformIT, you'll see a reference to those.

    Buck

    Thursday, December 21, 2006 4:08 AM

All replies

  • It seems there are few things going on here. Let's take a moment and break each one down.

    First, the account used to install SQL Server is normally a System Administrator. What's more important are the accounts used to start the services for SQL Server. You have a few choices there, but most often it's best to use a regular account, rather than LocalSystem or NetworkingSystem. You can find out more about that in Books Online searching for "Services" . When you install SQL Server, by default the local Windows Administrator's group is placed in the SQL Server sysadmin Role, which allows all rights for everything. Other users don't have access at all (yet).

    Security inside SQL Server is independent of the installation or the startup accounts. Since the "Glen" account is a local administrator, he can do anything he wants in SQL Server. If you created a "TestUser" server login, they can connect, but they can't do anything else. You'll need to assign them a database, create a user in the database tied to the "TestUser" login, and grant rights there.

    There are server-level rights, and database-level rights. The CREATE DATABASE statement is a server-level right, and most users don't need that.

    Books Online has a great set of topics on SQL Server Security that will help you sort all this out. You can also see my articles on Security starting here:

    http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=35&rl=1

    Buck Woody

    • Proposed as answer by JohnLazo Tuesday, January 15, 2013 3:56 PM
    Wednesday, December 20, 2006 3:06 PM
  •  

    Thanks for a quick reply Buck.

    I have another question related to your answer.  You mentioned about "LocalSystem" or "NetworkingSystem".

    Are you pertaining to the Log On tab section "Log on as:" found in the  SQL Server (SQLEXPRESS) Properties in the SQL Server Configuration Manager?  Are you advising me to select "This account:" and create a user from there?

    I will read more on the Online Books at the same time.  This is to know the database that I need to assign to "TestUser".  I am guessing here if you are talking about the application database or the database originally in the SQLEXPRESS like the master, model, etc.

    Thanks again.

     

     

     

    Thursday, December 21, 2006 2:07 AM
  • That's right. You can also set that in the Services applet of the Control Panel.

    The application database is the only one that needs a user account, in addition to a server login. If you check that site on InformIT, you'll see a reference to those.

    Buck

    Thursday, December 21, 2006 4:08 AM

  • Hi guys I felt I should drop a line on this issue since I just finished fighting it with some seed from the replies Buck gave.

    I had created an SMO app for attaching my database. That failed on running it on Vista Home Premium Edition, but works fine on XP.

    Then I peeped into the Event viewer on Vista and got confirmation it was a permission issue.

    I then wrote a little bat file with the following:

    osql -S "(local)\sqlinstance" -E -i "\update\isql.txt" -o "\update\osql.txt"

    given that "isql.txt" exists in the executing
    folder.

    isql.txt contains:

    EXEC sp_attach_db @dbname = N'MyDatabase',
    @filename1 = N'C:\Data\db.mdf',
    @filename2 = N'C:\Data\db_log.ldf' ;
    Go


    After running this bat, I got the error:

    "create database permission denied in database master"

    I then tried turning off User Account Control in Vista by going to Control Panel->User Accounts->Turn User Account Control on or off.

    This means that Vista would allow my account(an admininstrator) to behave like an administrator, at least as far as common file operations are concerned!

    Then Vista requires that you restart the machine.

    After that I could now attach my database.

    I think that the moral of the story is that Vista failed to allow SQL Express 2005 to attach my database. I didn't need to install SQL Express 2005 SP2.

    There may well be a security risk here but Vista should not affect SQL operation in the way it did.




    Wednesday, April 09, 2008 10:25 AM
  •  Irvinhoe wrote:


    I then tried turning off User Account Control in Vista by going to Control Panel->User Accounts->Turn User Account Control on or off.

    This means that Vista would allow my account(an admininstrator) to behave like an administrator, at least as far as common file operations are concerned!




     

    Thanks, that did the trick for me.

    Tuesday, August 26, 2008 4:16 AM
  • If you right click "SQL Server Management Studio" and choose "Run as Administrator" it will allow you to create new databases also.

     

     

     

     

     

    • Proposed as answer by DCense Saturday, July 25, 2009 5:29 AM
    Thursday, November 27, 2008 6:59 PM
  • Create DATABASE PERMISSION DENIED IN DATABASE 'master" ?

    This error is specifically related to the permission for that user within sql server.  If you do have permissions with that user account to create a database then you may want to check the UAC within Vista (only vista).    Try disabling that.  Here are instructions on how to do this:    Launch MSconfig (run > msconfig), at the msconfig- system configuration tool select the tools tab.   Within the tools tab there is a DISABLE UAC, highlight and click the launch button.    A command prompt window will appear indicating the operation was created successfully.   Note:  disabling this feature requires a reboot or your box.   When you reboot the box try to create a database.

    Does this help?
    Sunday, February 08, 2009 8:47 PM
  • That's more I Like it.
    Cool
    Saturday, July 25, 2009 5:29 AM
  • ________________________________________________________

    Update: Nov 20th, 00:01 AM
    ________________________________________________________
     - 1 of 3 issues resolved.
       Q1. Error code 29506 on install of SSMSE resolved,
             see answer inserted at Q1(below)
    ________________________________________________________

    Update: Nov 20th, 06:28 AM
    ________________________________________________________
     - 1 of 3 issues resolved.
     - now a different error in Database connection(Conn.Open) statement...
    Able to manage users in database, have added the NETWORK SERVICE Account to database... per Jeff's instructions, and here we go the usual cascade of next errors to deal with:
    ...Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed...

    IF I USE:
    <add name="MyWebsite.My.MySettings.ConnString" connectionString="Data Source=MyDevMachine\SQLExpress;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf";Integrated Security=True;User Instance=True;" />
    Error:  Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

    IF I USE:
    <add name="MyWebsite.My.MySettings.ConnString" connectionString="Data Source=MyDevMachine\SQLExpress;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf";User ID=sa;Password=pwpwpwpw;"/>
    Error:  Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

    ________________________________________________________

    Update: Nov 21st, 09:45 AM
    ________________________________________________________

    IF I USE:
    <add name="MyWebsite.My.MySettings.ConnString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDB.mdf";Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
    Error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
    ________________________________________________________

    ORIGINAL POST Starts HERE

    Pardon my inexperience.

    Some background and the associated frustration:

    I have battled about every possible tripping obstacle one can get trying to get:

        - my old XP Pro, Visual Studio Pro 2008, SQLServer 2005 Express, IE 8 project
        - to work on my new WINDOWS 7 Pro, Visual Studio Pro 2008, SQLServer 2005 Express, IE 8

     

    I have read 100s of posts, and was able to resolve a slew of issues.
    If I had attempted to do everything that is suggested on line, I would more than likely have to reinstall.  I was careful to read and read until I found what I thought would take me a step farther.  In view of the above,
    please try to provide the direct solution to my problem as described rather than launching me into the best practices that entail having to know a system well to be able to manage it.  I intend to get versed at a later time, now I need a solution to keep moving and resume at the level I was on my original system.

    Everyone knows how to turn on features under ‘Programs and Features’ in Vista or WIN 7.

    But hardly anyone can tell me if it is IIS 7 or 7.5.  I believe they are one and the same.

    I turned on everything in the features as instructed on various postings.

    I got an old 6.0 IIS interface until I installed IIS 7 Manager, now I get the new
    IIS Manager 7 panes.


    XP Pro: I was able to connect and manage the database on XP Pro with either my MSDE Database manager(Vale Software) or the Visual Studio Server Explorer.

    Note: the ‘@’ character is substituted for the ANGLE BRACKETS

     

        @!-- add key="MyconnectionString" value="SERVER=ComputerName\SQLServerName;DATABASE=MyDatabase;Password=xxxxxxxx;
    User ID=sa"/
    --@

    C:\Program Files\Microsoft SQL Server\MSSQL$SQLServerName\Data\MyDatabase.mdf and (.ldf)

     

    Under the XP Pro scenario I could backup the database file, under various names, like data1, data2,…

    I could also copy back any of the backups and rename it ‘MyDatabase’, and all functioned without a glitch.

     

    Although the System Admin User (sa) is used in the connection string, I always thought that was setup as a Windows Authentication scenario, hence I assumed the credentials were not used.


    WIN 7:

    My old XP Pro box(32bit computer) gave out, so I got new hardware, Dual Core 64Bit(x86) with WINDOWS 7.

     

    I am able to run the site everything looking tight.  I can sign-on(login) via the web site.

    Thus it is seeing the database fine.

    I can access the database via the Visual Studio Explorer, query the data, make table structure changes, etc.

     

    When I switch from the Visual Studio Development Server to the local IIS Server, the open statement in the code fails:

     

    C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\MyDatabase

     

    System.Security.Principal.WindowsIdentity.GetCurrent().Name returns: “IIS APPPOOL\DefaultAppPool“

     

    If I use this connection string:

    @add name="SiteName.My.MySettings.ConnString" connectionString="Data Source=ComputerName \SQLExpress;AttachDbFilename=&quot;C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase.mdf&quot;;User ID=sa;Password=xxxxxxxx;"/@

    I get this error:

    Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection.

     

    If I use this connection string:

    <add name="SiteName.My.MySettings.ConnString" connectionString="Data Source= ComputerName \SQLExpress;AttachDbFilename=&quot;C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase.mdf&quot;;Integrated Security=True;User Instance=False;"/>

    I get this error:

    CREATE DATABASE permission denied in database 'master'.

    An attempt to attach an auto-named database for file C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyDatabase.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

     

    Q1. Can someone educate me just enough to get passed this obstacle.

    I feel certain there are 3 simple ways to set this up.

    I am not concerned about security… this is my personal machine, I just need access so I can resume my work… I will decipher the details later and educate myself to understand clearly what WINDOWS AUTH means, etc.

    ________________________________________________________

    SOLUTION to Q1: (Q1 as more than 1 solution... here is 1 of them)
    - open a command prompt (cmd.exe in accessories)
      right-clik it and start it in admin mode.
    - Change folder to where your .......msi is:
      cd C:\Users\me\Downloads\SSMSE (SQLServer Mngmnt) <enter>
    - Execute the installer:
      SQLServer2005_SSMSEE_x64.msi <enter>
    ________________________________________________________

    My added questions would be:

    Q2. I hope to stop using the MSDE(Vale Software) product to access and admin the database.

    Both locally and on the HOST(on the Internet).

    SQLServer Explorer does not provide the options to do that.  I cannot manage users, for example. The Vale software allowed me to do that.

    Is there not a tool that I can download from the Microsoft site that allows to perform basic admin?  It must work both locally and remotely or I will keep the Vale Software.

     

    Q3. I also notice that things I took for granted are no longer so.

    Example: when I reinstalled, IE 8 was also re/installed… the Client Side Debugging switch is off by default, thus I struggled to discover why my debugger would not see the javascript, until I read a million pages on the net…

    I wonder what else needs to be addressed in IE 8 to avoid a later obstacle.  Any hints?

     

    Thank you in advance.  No doubt, thank you so very much in advance.


    SPA
    Wednesday, November 18, 2009 11:12 AM

  • A night ago I know I swept thru a few settings and fell asleep at the keyboard, so I shutdown everything and went to rest, abruptly.  I am not sure what I changed.

    Today... I decided to make some minor COSMETIC changes to a page that has a datagrid on it, not even thinking the datagrid would fail.  ...and the grid came up loaded with data ???

    I quickly tested the login page that I was using to resolve this IIS/ASP.NET data access issue, and poof just like that the login page works too!

    I shall come back here to post after I have investigated and can prove what change I made did the trick.

    ...and then onto the next solution... getting the SQLManager Studio to connect to the HOST on the Internet, Can it do that?



    SPA
    Monday, November 30, 2009 12:43 PM
  • .
    The time I lost dealing with this issue has placed me in a bind...  I still have not had a chance to review why this is now working.
    ...but I will, and I will post here... 1 week maybe.

    Concurrent logins from Management studio, Web Page(.NET App), or Server Explorer in Visual Studio still require either a disconnect to go to other tool, or a reStart of the SQLEXPRESS Service... ?
    ...even though the setting for unlimited logins is set to 0.

    Thank you, at least I am able to work now, a bit slower but not at a screeeching halt!
    I'LLBEBAK!

    UPDATE:  Tue, Dec, 15th, 2009 12:45PM
    TO AVOID POSTING TOO MANY TIMES, I updated here: 

    REMOTE CONNECTION:
    Concurrent logins from my app, and from the SQL Server Studio Management tool are not a problem.

    LOCAL CONNECTION:
    I get a database error that the User(WINDOWS Currently signed ON user[Me, administrator]) fails.
    Same user signs on fine from either the web APP or the SQLServer tools as long as no other APP/tool/... are SigneON.
    That tells me that the problem is at the Database setup level perhaps!  ...could still be in the IIS setup since each scenario has its own IIS server.
    There must be a setting in the database that allows multiple SIGNONs for a user.
    .
    I'LLBEBAK

    SPA
    Saturday, December 12, 2009 10:37 PM
  • I am waiting with baited breath - and many sleepless nights trying to solve the CREATE DATABASE permission denied in database 'master'.

    on Vista Home Premium, SQLEXPRESS 2008, IIS7.0, SQL Management Studio. I have given my db user all possible rights in every possible place!
    Wednesday, January 20, 2010 2:30 AM
  • If you right click "SQL Server Management Studio" and choose "Run as Administrator" it will allow you to create new databases also.

    That is the best idea, thank you  :)

     

     

     

     


    Tuesday, January 26, 2010 3:22 AM
  • If you right click "SQL Server Management Studio" and choose "Run as Administrator" it will allow you to create new databases also.

     

     

     

     i'm very very thankfull to u,after a week i'm very use full abt ur answer,so i'm very happy nd proud of ur answer..thank u soo much

     


    Wednesday, February 10, 2010 7:44 AM
  • I have Windows 7 and am having the same problem but can't find the "user Account Control on or off" selection.  My account is shown as an administrator.  Any suggestions?
    Dennis in Longview
    Saturday, December 11, 2010 11:30 AM
  • excellent!!!
    Thursday, February 10, 2011 8:34 PM
  • Yes............ This method is absolutely correct. After long hours of trying it out otherwise this method did work at 1 go.........:)

    Monday, April 11, 2011 6:32 AM
  • Thanks . i am able to create new database but not able to restore the .bak files .could you please help me out
    Saturday, September 17, 2011 4:47 PM