Search code examples
sql-serversql-server-2017

Can't alter table in schema with alter permission on schema

I want a user to be able to run alter table to add a column to a table in a particular schema, dvi, and the user has been granted alter permission on that schema, but when that user runs a query like

alter table
"db"."dvi"."mytablename"
add "columnname" varchar(50) default NULL

I get the error

42000
21050
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Only members of the sysadmin fixed server role or db_owner fixed database role can perform this operation. Contact an administrator with sufficient permissions to perform this operation.

I don't want to grant the user db_owner or sysadmin because they shouldn't be able to alter objects in other tables. Is there any other way to let the user do perform this action? I'm using SQL Server 2017 version 14.0.2027.2

Edit: the database is the publisher for a transactional replication, which might be a factor in causing the error

Solution

  • The issue was that this table is part of a replication, so any alter table run on this table would trigger a stored procedure that can only be executed by db_owners or sysadmins, so that prevents the alter from being completed.