Sample Header Ad - 728x90

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

1 vote
1 answer
887 views
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 1. create a login and add a user to the database I created 1. I don't add any database roles to the user. 1. 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
![SSMS screenshot](https://i.sstatic.net/lFk2w.png)
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?
Asked by AxD (133 rep)
Oct 19, 2021, 11:25 PM
Last activity: Oct 20, 2021, 01:49 AM