Sample Header Ad - 728x90

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