asked
Changing the size of a column referenced by a schema-bound view in SQL Server
Hi, I'm trying to change the size of a column in sql server using: ALTER TABLE [dbo].[Address] ALTER COLUMN [Addr1] [nvarchar](80) NULL where the length of **Addr1** was originally 40. It failed, raising this error: The object 'Address_e' is dependent on column 'Addr1'. ALTER TABLE ALTER COLUMN Addr1 failed because one or more objects access this column. I've tried to read up on it and it seems that because some views are referencing this column and it seems that SQL Server is actually trying to drop the column that raised the error. **Address_e** is a view created by the previous [DBA][1]. Is there any other way I can change the size of the column? Thank you Hari [1]:
http://kosmiktechnologies.com/sql-dba-training-in-hyderabad/
Script out the Address_e view as drop and create, drop the view, modify the column, recreate the view. As the view is schemabound, the only way to modify the underlying schema is to remove the views dependency on binding.
@kosmik5 Because the view needs to be dropped and created anew, please make sure that you script out the explicit grants to it (if any). After the view is dropped, Addr1 column is altered, and the view is created, apply the explicit grants. This needs to be done in order to avoid a situation when some login which was able to select data from the view is now greeted with permission denied error.
17 People are following this question.
