1

All I'm trying to do is query a view and I'm receiving the following error:

CREATE TABLE permission denied in database 'ELITEPROD'

I'm coding ASP.NET MVC5 using Entity Framework. Here is the code I'm running:

string sql = @"SELECT ref.*, sts.[IMPORT_STATUS]
               FROM [ELITEPROD].[dbo].[SPEECH_PSYCH_WRITER] AS ref
               LEFT JOIN dbo.SPEECH_PSYCH_IMPORT_STATUS AS sts 
               ON sts.REQUEST_ID = ref.REFERRAL_ID
               WHERE REFERRAL_TYPE = @referType AND REFERRAL_CONTACT = @username 
               AND (sts.[IMPORT_STATUS] IS NULL OR sts.[IMPORT_STATUS] <> '');";

var referrals = eLite.Database.SqlQuery<Referral>(sql,
    new SqlParameter("@referType", referType),
    new SqlParameter("@username", username)
).ToList();

In the web config file I have 2 databases configured. This query is happening on the second one. Not the DefaultConnection. The db is not out of memory and I am not (intentionally) trying to create a table. Any ideas?

Here is the full error:

[SqlException (0x80131904): CREATE TABLE permission denied in database 'ELITEPROD'.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +212
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +81
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +630
System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4233
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite) +952
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +304
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +282
System.Data.Entity.Infrastructure.Interception.<>c.<NonQuery>b__4_0(DbCommand t, DbCommandInterceptionContext`1 c) +12
System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch(TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed) +72
System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext) +427
System.Data.Entity.Internal.InterceptableDbCommand.ExecuteNonQuery() +103
System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) +109
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext) +83
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext) +65
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) +155
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext) +359
System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection) +509
System.Data.Entity.Migrations.<>c__DisplayClass61_0.<ExecuteStatements>b__0() +19
System.Data.Entity.SqlServer.<>c__DisplayClass2_0.<Execute>b__0() +10
System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Func`1 operation) +189
System.Data.Entity.SqlServer.DefaultSqlExecutionStrategy.Execute(Action operation) +78
System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction) +211
System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements) +7
System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto) +938
System.Data.Entity.Migrations.DbMigrator.AutoMigrate(String migrationId, VersionedModel sourceModel, VersionedModel targetModel, Boolean downgrading) +589
System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId) +424
System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration) +452
System.Data.Entity.Migrations.<>c__DisplayClass42_0.<Update>b__0() +13
System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase) +422
System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) +78
System.Data.Entity.Internal.DatabaseCreator.CreateDatabase(InternalContext internalContext, Func`3 createMigrator, ObjectContext objectContext) +89
System.Data.Entity.Internal.InternalContext.CreateDatabase(ObjectContext objectContext, DatabaseExistenceState existenceState) +116
System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +218
System.Data.Entity.CreateDatabaseIfNotExists`1.InitializeDatabase(TContext context) +136
System.Data.Entity.Internal.<>c__DisplayClass66_0`1.<CreateInitializationAction>b__0() +76
System.Data.Entity.Internal.InternalContext.PerformInitializationAction(Action action) +60
System.Data.Entity.Internal.InternalContext.PerformDatabaseInitialization() +357
System.Data.Entity.Internal.<>c.<InitializeDatabase>b__58_0(InternalContext c) +9
System.Data.Entity.Internal.RetryAction`1.PerformAction(TInput input) +129
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabaseAction(Action`1 action) +196
System.Data.Entity.Internal.LazyInternalContext.InitializeDatabase() +86
System.Data.Entity.Internal.InternalContext.Initialize() +30
System.Data.Entity.Internal.LazyInternalContext.get_ObjectContext() +13
System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(String sql, Nullable`1 streaming, Object[] parameters) +109
System.Data.Entity.Internal.InternalContext.ExecuteSqlQueryAsIEnumerator(String sql, Nullable`1 streaming, Object[] parameters) +68
System.Data.Entity.Internal.InternalContext.ExecuteSqlQuery(Type elementType, String sql, Nullable`1 streaming, Object[] parameters) +181
System.Data.Entity.Internal.InternalSqlNonSetQuery.GetEnumerator() +41
System.Data.Entity.Infrastructure.DbRawSqlQuery`1.GetEnumerator() +30
System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) +174
System.Linq.Enumerable.ToList(IEnumerable`1 source) +54
Report_Writers.Controllers.<Index>d__3.MoveNext() in C:\Users\craftjer\source\repos\report-writer-v2\Report Writers\Controllers\ReportsController.cs:59
System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) +102
System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +64
System.Web.Mvc.Async.TaskAsyncActionDescriptor.EndExecute(IAsyncResult asyncResult) +97
System.Web.Mvc.Async.<>c__DisplayClass8_0.<BeginInvokeAsynchronousActionMethod>b__1(IAsyncResult asyncResult) +17
System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethod(IAsyncResult asyncResult) +32
System.Web.Mvc.Async.<>c__DisplayClass11_0.<InvokeActionMethodFilterAsynchronouslyRecursive>b__0() +58
System.Web.Mvc.Async.<>c__DisplayClass11_2.<InvokeActionMethodFilterAsynchronouslyRecursive>b__2() +228
System.Web.Mvc.Async.<>c__DisplayClass7_0.<BeginInvokeActionMethodWithFilters>b__1(IAsyncResult asyncResult) +10
System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeActionMethodWithFilters(IAsyncResult asyncResult) +34
System.Web.Mvc.Async.<>c__DisplayClass3_6.<BeginInvokeAction>b__4() +35
System.Web.Mvc.Async.<>c__DisplayClass3_1.<BeginInvokeAction>b__1(IAsyncResult asyncResult) +100
System.Web.Mvc.Async.WrappedAsyncResult`1.CallEndDelegate(IAsyncResult asyncResult) +10
System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
System.Web.Mvc.Async.AsyncControllerActionInvoker.EndInvokeAction(IAsyncResult asyncResult) +27
System.Web.Mvc.<>c.<BeginExecuteCore>b__152_1(IAsyncResult asyncResult, ExecuteCoreState innerState) +11
System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
System.Web.Mvc.Controller.EndExecuteCore(IAsyncResult asyncResult) +45
System.Web.Mvc.<>c.<BeginExecute>b__151_2(IAsyncResult asyncResult, Controller controller) +13
System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +22
System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
System.Web.Mvc.Controller.EndExecute(IAsyncResult asyncResult) +26
System.Web.Mvc.Controller.System.Web.Mvc.Async.IAsyncController.EndExecute(IAsyncResult asyncResult) +10
System.Web.Mvc.<>c.<BeginProcessRequest>b__20_1(IAsyncResult asyncResult, ProcessRequestState innerState) +28
System.Web.Mvc.Async.WrappedAsyncVoid`1.CallEndDelegate(IAsyncResult asyncResult) +29
System.Web.Mvc.Async.WrappedAsyncResultBase`1.End() +49
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +28
System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
System.Web.CallHandlerExecutionStep.InvokeEndHandler(IAsyncResult ar) +152
System.Web.CallHandlerExecutionStep.OnAsyncHandlerCompletion(IAsyncResult ar) +126
Jeff Craft
  • 11
  • 5
  • Does this answer your question? [CREATE TABLE permission denied in database 'tempdb'](https://stackoverflow.com/questions/6563565/create-table-permission-denied-in-database-tempdb) – D M Jun 18 '21 at 15:18
  • [Similar question](https://stackoverflow.com/questions/42762839/create-table-permission-denied-in-database-sql-server-2008-r2-apsaradb-for-rds) where the issue was the DB instance being out of memory. – D M Jun 18 '21 at 15:19
  • Another possible dupe target: [Listing table results in “CREATE TABLE permission denied in database” ASP.NET - MVC4](https://stackoverflow.com/questions/14727397/listing-table-results-in-create-table-permission-denied-in-database-asp-net/21564462) – D M Jun 18 '21 at 15:21

3 Answers3

4

Your call stack goes through

System.Data.Entity.Database.Create(DatabaseExistenceState existenceState) +218

which means that your target database doesn't exist or isn't up-to-date with your model, and EF is trying to update the schema.

Perhaps your connection string is wrong, but in any case you should create/migrate the database schema ahead-of-time, not at startup.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 2
    Or alternatively: completely disable EF Migrations and do it manually with SSDT. I find EF Migrations cause more trouble than they're worth in projects of any real complexity - or when I need to use new SQL Server features (_still waiting_ for Temporal Table, Indexed View, Table Partitoning, and Windowing-Function support in EF...) – Dai Jun 18 '21 at 15:23
0

In the Models folder I set up ELiteDbContext.cs and I had this definition:

public DbSet<Referral> Referrals { get; set; }

Since this was just a view I was querying and will never write to it all I had to do is comment out the DbSet line and everything worked.

Jeff Craft
  • 11
  • 5
  • 1
    Nevertheless, it's always a good idea to [turn off automatic migration](https://stackoverflow.com/q/18667172/861716) when not working code-first. – Gert Arnold Jun 18 '21 at 19:42
0

It could also be a permissions issue. Make sure the account the application is running under has select permissions to the view. You may be missing a grant.

Mike B
  • 61
  • 1
  • 3