What is the permission needed for a SQL Server login to change a tables name?

0

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, Action1 wrapCloseInAction)
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
1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
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, Action1 wrapCloseInAction)
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
1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary
2 parameterValues)
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?

sql-server
entity-framework-core
ef-migrations
asked on Stack Overflow Jun 20, 2018 by Alexander Burke • edited Jun 20, 2018 by marc_s

1 Answer

0

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

answered on Stack Overflow Jun 20, 2018 by Alexander Burke

User contributions licensed under CC BY-SA 3.0