Alter table alter column does not work with user defined types
8
votes
1
answer
348
views
A user with db_datareader, db_datawriter, db_ddladmin and db_securityadmin roles can add columns with a user defined type, but cannot alter those.
For example: Lets say we create a user defined type called MyDateType of type DateTime. We want to add a MyDateType column to table Person, fill it with something and then set it to not null.
ALTER TABLE Person ADD SomeDate MyDateType NULL; -- This works
UPDATE Person SET SomeDate = OtherDate; -- This works
ALTER TABLE Person ALTER COLUMN SomeDate MyDateType NOT NULL; -- User has no permission error
When we change that last statement to:
ALTER TABLE Person ALTER COLUMN SomeDate DateTime NOT NULL; -- This works!
So, using a direct datatype lets me change the column, but using our user defined type does not, which is strange to me, because adding works.
I could also solve it by using a default value and adding the column as not null straight away, but I am more interested in why alter column behaves this way. Anyone no why?
The exact error is this:
> Msg 15247, Level 16, State 5, Line 1
> User does not have permission to perform this action.
**Full repro script**
REVERT;
GO
DROP TABLE IF EXISTS Person
DROP TYPE IF EXISTS MyDateType
DROP USER IF EXISTS MyUser;
CREATE TYPE MyDateType FROM DATETIME;
CREATE TABLE Person (OtherDate DATETIME);
CREATE USER MyUser WITHOUT LOGIN;
ALTER ROLE db_datareader ADD MEMBER MyUser
ALTER ROLE db_datawriter ADD MEMBER MyUser
ALTER ROLE db_ddladmin ADD MEMBER MyUser
ALTER ROLE db_securityadmin ADD MEMBER MyUser
GO
GRANT REFERENCES ON TYPE::MyDateType TO MyUser
--GRANT EXECUTE ON TYPE::MyDateType TO MyUser
ALTER TABLE Person ADD SomeDate MyDateType NULL; -- This works
GO
EXECUTE AS User='MyUser'
UPDATE Person SET OtherDate = SomeDate; -- This works
UPDATE Person SET SomeDate = OtherDate; -- This works
ALTER TABLE Person ALTER COLUMN SomeDate MyDateType NOT NULL; -- User has no permission error
Asked by Plekuz
(83 rep)
Aug 29, 2025, 11:22 AM
Last activity: Aug 31, 2025, 06:36 AM
Last activity: Aug 31, 2025, 06:36 AM