permission to create dbo.proc but not drop table

Hi group,
is there a way to create a role, assign permission to it so that it can
create procedures with dbo as owner, and not able to drop tables?
QuentinAlthough you can't create a user role for this, you can add a user to the
db_ddladmin fixed database role and then DENY those statement permissions
you don't want the user to have For example:
EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
DENY CREATE TABLE TO MyUser1
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi group,
> is there a way to create a role, assign permission to it so that it can
> create procedures with dbo as owner, and not able to drop tables?
> Quentin
>|||Dan,
thanks for the response. I have been there -- you can still drop tables.
Quentin
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> Although you can't create a user role for this, you can add a user to the
> db_ddladmin fixed database role and then DENY those statement permissions
> you don't want the user to have For example:
> EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> DENY CREATE TABLE TO MyUser1
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > Hi group,
> >
> > is there a way to create a role, assign permission to it so that it can
> > create procedures with dbo as owner, and not able to drop tables?
> >
> > Quentin
> >
> >
>|||> thanks for the response. I have been there -- you can still drop tables.
Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
case for including this functionality in a future SQL Server version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Quentin Ran" <ab@.who.com> wrote in message
news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> Dan,
> thanks for the response. I have been there -- you can still drop tables.
> Quentin
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > Although you can't create a user role for this, you can add a user to
the
> > db_ddladmin fixed database role and then DENY those statement
permissions
> > you don't want the user to have For example:
> >
> > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > DENY CREATE TABLE TO MyUser1
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "Quentin Ran" <ab@.who.com> wrote in message
> > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > Hi group,
> > >
> > > is there a way to create a role, assign permission to it so that it
can
> > > create procedures with dbo as owner, and not able to drop tables?
> > >
> > > Quentin
> > >
> > >
> >
> >
>|||> I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
Thanks Dan. Did that.
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OexgQ8DNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > thanks for the response. I have been there -- you can still drop
tables.
> Sorry about that. I tested my suggestion with CREATE TABLE but not DROP
> TABLE. I suggest you send this to sqlwish@.microsoft.com to present your
> case for including this functionality in a future SQL Server version.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Quentin Ran" <ab@.who.com> wrote in message
> news:uVpgV1DNEHA.2388@.TK2MSFTNGP09.phx.gbl...
> > Dan,
> >
> > thanks for the response. I have been there -- you can still drop
tables.
> >
> > Quentin
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:#7xngl9MEHA.2736@.TK2MSFTNGP11.phx.gbl...
> > > Although you can't create a user role for this, you can add a user to
> the
> > > db_ddladmin fixed database role and then DENY those statement
> permissions
> > > you don't want the user to have For example:
> > >
> > > EXEC sp_addrolemember 'db_ddladmin' ,'MyUser'
> > > DENY CREATE TABLE TO MyUser1
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > "Quentin Ran" <ab@.who.com> wrote in message
> > > news:OxJQ8o4MEHA.1312@.TK2MSFTNGP12.phx.gbl...
> > > > Hi group,
> > > >
> > > > is there a way to create a role, assign permission to it so that it
> can
> > > > create procedures with dbo as owner, and not able to drop tables?
> > > >
> > > > Quentin
> > > >
> > > >
> > >
> > >
> >
> >
>