MS SQL Server security - 21776 Error by EM when a view column name is changed.

Author

21776 Error by EM when a view column name is changed.

BlairH

2005-08-31, 11:23 am

I am wondering if there is a sp or some other method to fix an issue that
arises when you change the name of a column in a view.

The scenario is:
1. Create the view
2. Alter the view, changing the name of one of the fields
3. Within EM, select the view | Properties | permissions, and then check the
column permissions - you get an error "21776 [SQL-DMO] The name
'<newcolumnname>' was not found in the columns collection..."

The BOL item on ALTER VIEW warns that if you have permissions defined on a
view column, and then change the name of the column, the permissions do not
follow, BUT, in addition to this, you cannot even see the NEW column to
assign the permissions again in the above scenario.

Has anyone run into this?

Thanks

--
Best regards,
Blair

Andrew J. Kelly

2005-08-31, 1:23 pm

Check out sp_refreshview in BooksOnLine.

--
Andrew J. Kelly SQL MVP

"BlairH" <BlairH@discussions.microsoft.com> wrote in message
news:8AB571A3-779F-497A-A707- 703B66FEF13E@microso
ft.com...
>I am wondering if there is a sp or some other method to fix an issue that
> arises when you change the name of a column in a view.
>
> The scenario is:
> 1. Create the view
> 2. Alter the view, changing the name of one of the fields
> 3. Within EM, select the view | Properties | permissions, and then check
> the
> column permissions - you get an error "21776 [SQL-DMO] The name
> '<newcolumnname>' was not found in the columns collection..."
>
> The BOL item on ALTER VIEW warns that if you have permissions defined on a
> view column, and then change the name of the column, the permissions do
> not
> follow, BUT, in addition to this, you cannot even see the NEW column to
> assign the permissions again in the above scenario.
>
> Has anyone run into this?
>
> Thanks
>
> --
> Best regards,
> Blair

BlairH

2005-09-01, 3:23 am

Thanks very much Andrew. That's exactly what I was looking for. In
addition, it apears that you have to restart EM in order for the fix to take
effect. (but it does)

Thanks again.
--
Best regards,
Blair

"Andrew J. Kelly" wrote:

> Check out sp_refreshview in BooksOnLine.
>
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "BlairH" <BlairH@discussions.microsoft.com> wrote in message
> news:8AB571A3-779F-497A-A707- 703B66FEF13E@microso
ft.com...
>
>
>

Andrew J. Kelly

2005-09-01, 3:23 am

All you should have needed to do after issuing sp_refreshview was to refresh
that node in EM. But restarting works too<g>.

--
Andrew J. Kelly SQL MVP

"BlairH" <BlairH@discussions.microsoft.com> wrote in message
news:5EF6E77B-79A3-46AB-8070- 3842A950407F@microso
ft.com...[color=darkred]
> Thanks very much Andrew. That's exactly what I was looking for. In
> addition, it apears that you have to restart EM in order for the fix to
> take
> effect. (but it does)
>
> Thanks again.
> --
> Best regards,
> Blair
>
>
> "Andrew J. Kelly" wrote:
>

Sponsored Links