CREATE DATABASE permission denied in database 'master'. Cannot attach

Question

In a T-SQL script, using a connection string like this:

Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Temp\ContextTest.mdf;Initial Catalog=ContextTest;Integrated Security=True

... I ...

1. create a SQL Server LocalDB database
2. create a login and add a user to the database I created
3. I don't add any database roles to the user.
4. I create a table and grant SELECT, INSERT, UPDATE, DELETE, REFERENCE rights to the user

The script looks similar to this:

CREATE LOGIN [U] WITH PASSWORD = '***'
CREATE USER [U] FROM LOGIN [U]
CREATE TABLE [U].[TestEntities]
  ( Id INT PRIMARY KEY IDENTITY
  , Name NVARCHAR(100) NOT NULL UNIQUE
  )
GRANT INSERT, DELETE, SELECT, REFERENCES, UPDATE ON [U].[TestEntities] TO [U];

Next, I'm using SSMS to inspect everything, using the credentials I just created.

When I connect in SSMS using the newly created user, I can easily query the [U].[TestEntities] table using a query like this:

SELECT COUNT(*) FROM U.TestEntities

Then, if I do the same in .NET, I get an error message claiming I'd require CREATE DATABASE rights:

using namespace System.Data.SqlClient

try {
    $con = [SqlConnection]::new('Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=C:\Temp\ContextTest.mdf;Initial Catalog=ContextTest;User ID=U;Password=***')
    $cmd = $con.CreateCommand()
    $cmd.CommandText = 'SELECT COUNT(*)
FROM [U_MCL].[TestEntities] AS [t]'

    $con.Open()
    $cmd.ExecuteScalar()
} finally {
    $con.Dispose()
}

MethodInvocationException: C:\Temp\Test.ps1:9:2
Line |
   9 |      $con.Open()
     |      ~~~~~~~~~~~
     | Exception calling "Open" with "0" argument(s): "CREATE DATABASE permission denied in database 'master'. Cannot attach the file
     | 'C:\Temp\ContextTest.mdf' as database 'ContextTest'."

What am I missing?

Answer 1

If I read your question correctly, when you are doing this in SSMS you are creating the database in the context of your account (using integrated security), then after creating the database you are connecting with the limited credentials to query the database. This works fine because your account has permission to create databases but the limited credential does not.

When you are connecting with C#, you are doing so with the limited credentials, and passing in AttachDbFilename which is causing SQL Express to try to create a new database in that context, but the limited credentials don't have permission to create/attach a database like your account does, hence it fails. If you've already created the database you shouldn't need to specify AttachDbFilename in your C# connection string, you can just connect like you are doing the second time in SSMS.

AttachDbFilename is not the ideal way to do this, so it would also be worth reading this blog post from Aaron Bertrand for more details about how to approach this better.