none
SQL Authentication -SQL Express 2008, Web application

    Question

  • I'm using the NerdDinner app and SQL Express 2008. I have succesfully created a SQL User Account in SSMS and granted rights to only the NerdDinner mdf, can log into SSMS using this account and work with the database, provided the NerdDinner mdf is attached to SQL Express.

    My problem comes when trying to use the sql account to connect to the database in VS2008 or create a UDL Data Link. In both cases I get an error stating "CREATE DATABASE permission denied in database 'master'.

    What setting, where needs to be made so that this working SQL Account and pwd will work in VS2008 to connect to the database. The app I'm building is a web app that will be hosted on a commercial provider so I have to use a SQL Account. I'm testing  all of this on Win7, if it makes a differnece.


    Steve
    Friday, February 19, 2010 1:34 AM

Answers

  • Hi Steve,
    Were you connecting your SQL Server 2008 Express with specifying AttachDbFileName to attach the database with the connection?
    I can reproduce your issue with the following code:
                SqlConnection cn = new SqlConnection(String.Format("server=localhost;database={0};attachdbfilename={1};user={2};password={3}", txtDB.Text, txtFilePath.Text, txtLogin.Text, txtPwd.Text));
                try
                {
                    cn.Open();
                    MessageBox.Show("CONN OK!");
                }
                catch (Exception e1)
                {
                    MessageBox.Show(e1.ToString());
                }


    If so, it requires CREATE DATABASE permission on the master database since there is an attach database operation. To make it work, you can try either of the following methods:
    1. Create an user for your SQL login in the master database and then grant CREATE DATABASE permission to it.
    For example:
    USE MASTER
    GO
    CREATE USER sql1 FROM LOGIN sql1
    GO
    GRANT CREATE DATABASE TO sql1
    GO

    2. Give the SQL login the fixed server role "dbcreator" in Management Studio.

    Hope this helps.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, February 19, 2010 9:50 AM
    Moderator

All replies

  • If your application is trying to create a new database , then the SQl account at least needs dbcreator privilege at the SQL server level.
    Thanks, Leks
    Friday, February 19, 2010 1:37 AM
    Answerer
  • The web app is not trying to create a database (where did that come from?). The database already exists and the sql user account is in the db_owner role on the database.

    If I use the sa account I can create a connection successfully to the NerdDinner mdf, but not from the specific user account I've created for application access.

    While the database is attached so SQLExpress a connection can be made with the account, but, if it is not attached a file level connection can not be made, instead the "CREATE DATABASE permission denied in database 'master' message appears. I think I should be able to make a file level connection using a SQL Account.



    Steve
    Friday, February 19, 2010 2:34 AM
  • Hi Steve,
    Were you connecting your SQL Server 2008 Express with specifying AttachDbFileName to attach the database with the connection?
    I can reproduce your issue with the following code:
                SqlConnection cn = new SqlConnection(String.Format("server=localhost;database={0};attachdbfilename={1};user={2};password={3}", txtDB.Text, txtFilePath.Text, txtLogin.Text, txtPwd.Text));
                try
                {
                    cn.Open();
                    MessageBox.Show("CONN OK!");
                }
                catch (Exception e1)
                {
                    MessageBox.Show(e1.ToString());
                }


    If so, it requires CREATE DATABASE permission on the master database since there is an attach database operation. To make it work, you can try either of the following methods:
    1. Create an user for your SQL login in the master database and then grant CREATE DATABASE permission to it.
    For example:
    USE MASTER
    GO
    CREATE USER sql1 FROM LOGIN sql1
    GO
    GRANT CREATE DATABASE TO sql1
    GO

    2. Give the SQL login the fixed server role "dbcreator" in Management Studio.

    Hope this helps.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Friday, February 19, 2010 9:50 AM
    Moderator