db_ddladmin role without 'drop' capability

Hello -
I'm looking for a way to assign a user db_ddladmin
permissions on a particular database, but without the DROP
functionality. I want them to be able to do everything
that the role entails, but not to be able to drop tables
etc. Any help would be greatly appreciated...thanks!
MatthewHi,
If you provide db_ddladmin role you cant restrict the user from drop
command. Because the DENY or REVOKE
command can not be granted.
So alternative is provide the roles 'db_datareader', 'db_datawriter' the
user and provide explicit grant to
create table,create proc,create function,create view.
Sample:-
sp_addrolemember 'db_datareader','user'
go
sp_addrolemember 'db_datawriter','user'
go
grant create table,create proc,create function,create view to <user>
Thanks
Hari
MCDBA
"Matthew" <anonymous@.discussions.microsoft.com> wrote in message
news:017601c46dd3$52520310$a301280a@.phx.gbl...
> Hello -
> I'm looking for a way to assign a user db_ddladmin
> permissions on a particular database, but without the DROP
> functionality. I want them to be able to do everything
> that the role entails, but not to be able to drop tables
> etc. Any help would be greatly appreciated...thanks!
> Matthew|||Thanks Hari, much appreciated!

>--Original Message--
>Hi,
>If you provide db_ddladmin role you cant restrict the
user from drop
>command. Because the DENY or REVOKE
>command can not be granted.
>So alternative is provide the
roles 'db_datareader', 'db_datawriter' the
>user and provide explicit grant to
>create table,create proc,create function,create view.
>Sample:-
>sp_addrolemember 'db_datareader','user'
>go
>sp_addrolemember 'db_datawriter','user'
>go
>grant create table,create proc,create function,create
view to <user>
>--
>Thanks
>Hari
>MCDBA
>"Matthew" <anonymous@.discussions.microsoft.com> wrote in
message
>news:017601c46dd3$52520310$a301280a@.phx.gbl...
DROP[vbcol=seagreen]
>
>.
>