I am building out my database using EF Core 2.1 code first migrations. I have altered a table to rename. The migration initially dropped the entire table and then read it with the new name, which had issues with foreign key constraints. I changed the migration to use the RenameTable
method instead like
migrationBuilder.RenameTable("TourSpecs", "TourSpecHistory");
When running update-database
I get the error
Failed executing DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @defaultSchema sysname = SCHEMA_NAME();
EXEC(N'ALTER SCHEMA ' + @defaultSchema + N' TRANSFER [TourSpecHistory].[TourSpecs];');
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (27ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
DECLARE @defaultSchema sysname = SCHEMA_NAME();
EXEC(N'ALTER SCHEMA ' + @defaultSchema + N' TRANSFER [TourSpecHistory].[TourSpecs];');System.Data.SqlClient.SqlException (0x80131904): Cannot find the object 'TourSpecs', because it does not exist or you do not have permission.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)
1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
ClientConnectionId:332393e8-8539-4c80-b24c-6478ec61b55b
Error Number:15151,State:1,Class:16
System.Data.SqlClient.SqlException (0x80131904): Cannot find the object 'TourSpecs', because it does not exist or you do not have permission.at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)
1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)
2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary
at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
ClientConnectionId:332393e8-8539-4c80-b24c-6478ec61b55b
Error Number:15151,State:1,Class:16
Cannot find the object 'TourSpecs', because it does not exist or you do not have permission.
Thinking this is just a permissions issue I ran the command
GRANT ALTER ON SCHEMA:: [dbo].[TourSpec] TO ApplicationLogin
But was hit with the error
Cannot find the schema 'dbo.TourSpec', because it does not exist or you do not have permission.
The login I am using to run the grant script has the ALTER ANY SCHEMA
permission. What do I need to do to get the correct permissions granted to my ApplicationLogin
user?
After @Sean Lange pointed out that I had an incorrect understanding of what was a schema and what an object, I successfully granted the permissions to my user but was still unable to run the migration. Looking through the RenameTable
method further, I discovered that it takes in 3 optional arguments at the end and that the migration was incorrectly try to alter a [TourSpecHistory].[TourSpecs]
table.
I changed the migration to run migrationBuilder.RenameTable("TourSpecs", "dbo", "TourSpecHistory", "dbo");
and it now works correctly
User contributions licensed under CC BY-SA 3.0