Issues with DDL_admin/Alter view

Question

Issues with DDL_admin/Alter view

Animal Magic

SSChampion

Points: 13964
More actions
October 5, 2009 at 10:11 am

#215981

Hi All,
One of my developers who is a member of db_ddladmin, db_datareader and db_datawriter cannot run an alter view command. i tried to replicate the issue and was able too quite easily.
I created a sql login and added it to the aforementioned fixed DB roles, then ran the following script
create view vw_bobtest
as
select top 10 * from employee
go
select * from vw_bobtest
go
alter view vw_bobtest
as
select top 5 * from employee
go
drop view vw_bobtest
it errors on the alter command with this message
Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12
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 find it strange that you can drop an existing view you dont even own yet you cant alter it. Am i missing something simple here??

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

could it be schema related, i mean that there are two schemas, say dbo. and bob. with both containing a view named "vw_bobtest

", and because the objects are not two part named, the script finds dbo.view and refuses an alter, but finds the bob.view?

does this work?, for example?(change "dbo" to the correct schema)

create view dbo.vw_bobtest

as

select top 10 * from dbo.employee

go

select * from dbo.vw_bobtest

go

alter view dbo.vw_bobtest

as

select top 5 * from dbo.employee

go

drop view dbo.vw_bobtest

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Animal Magic SSChampion Points: 13964 More actions · Answer 2

Hi,

Thanks for the reply. Unfortunately i tried that also with no success. I only use one schema, everyone uses dbo.

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 3

It looks your database is subject to replication, isn't it? I found article of Paul Ibison[/url] that discusses similar (I think) issue. It looks like you will have to drop and create the view or play with replication snapshot agent.

HTH

Piotr

...and your only reply is slàinte mhath

Animal Magic SSChampion Points: 13964 More actions · Answer 4

Piotr.Rodak (10/5/2009)
It looks your database is subject to replication, isn't it? I found article of Paul Ibison[/url] that discusses similar (I think) issue. It looks like you will have to drop and create the view or play with replication snapshot agent.
HTH
Piotr

It is indeed a replicated database. i will have a look at the article by Paul. Many thanks. at home now so will update the post tomorrow

Jatin Soni SSC Eights! Points: 933 More actions · Answer 5

Hello,

I am having the same problem. I had given developers DDL_Admin rights and it was working fine. I just need to setup merge replication for few tables and transactional for other tables. however developer started complaining that they are getting following error while altering procedure (although procedures are getting altered)

Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12

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 am not replicating any procedures. I am only replicating few tables.

Not sure why above procedure gets executed and why it needs to check owner.

can someone let me know how to resolve this issue? I do not wish to grant developers more rights.

Jatin Soni

Jatin Soni SSC Eights! Points: 933 More actions · Answer 6

Found the way to get rid of above error. I am posting solution here so that it will help others who are facing same issues. See the script I have created below.

/*Jatin: following trigger is enabled when replication is setup/altered

If it is Enabled then SQL developer will get following error while they try to

Alter any Procedure in database which is in replication.

Msg 21050, Level 16, State 1, Procedure sp_MSmerge_ddldispatcher, Line 12

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.

Resolution: Disable this database trigger. Since we are not replicating any procedure or functions,

it will not harm if this trigger do not fire.

*/

IF EXISTS (SELECT is_disabled,* FROM sys.triggers

WHERE parent_class_desc = 'DATABASE'

AND name = N'MSmerge_tr_alterschemaonly'

and is_disabled =0 --0 means DB Trigger is ENABLED

)

Begin

DISABLE TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE

End