Alter table permission to dbo

[M A Srinivas]

I have the following requirement

I am creating a login and database user 'test' on a database with dbo
role .
I want to remove create table , alter table permisions to this user.
I am able to revoke create table permission but alter table goes
through.
I gave a command deny insert,delete,u pdate on ssycolumns to test.
Still I am not able to prevent user altering schema . Alter table
successfully goes throgh.

I do not want to use datreader and datwriter role.
since I want user 'test' to create storred procedure with dbo owner

Is there a way to achieve this ?

Thanks

M A Srinivas

[Simon Hayes]

Re: Alter table permission to dbo


"M A Srinivas" <masri@vsnl.com > wrote in message
news:f7e90f78.0 309260634.3791a 935@posting.goo gle.com...[color=blue]
> I have the following requirement
>
> I am creating a login and database user 'test' on a database with dbo
> role .
> I want to remove create table , alter table permisions to this user.
> I am able to revoke create table permission but alter table goes
> through.
> I gave a command deny insert,delete,u pdate on ssycolumns to test.
> Still I am not able to prevent user altering schema . Alter table
> successfully goes throgh.
>
> I do not want to use datreader and datwriter role.
> since I want user 'test' to create storred procedure with dbo owner
>
> Is there a way to achieve this ?
>
> Thanks
>
> M A Srinivas[/color]

You can't prevent the user from modifying/dropping an existing object. If
you need to create objects with dbo owner, then the user must be in the
db_owner role, and that means he can modify/drop any dbo object. If you can
explain why you need the test user to create stored procedures, then perhaps
someone can suggest an alternative approach. Are you creating the procedures
dynamically, are you deploying new code to several server, etc.

Simon