Hi
If I create a database and give to the database user
dbo role membership he is able to change size of datafiles
mdf and ldf. How can i suppress that so that this is no longer
possible?
Kind regards
Mickey> If I create a database and give to the database user
> dbo role membership he is able to change size of datafiles
> mdf and ldf. How can i suppress that so that this is no longer
> possible?
Don't add the user to the db_owner role :-)
Membership of db_owner, sysadmin, etc. have powerful rights that (hopefully)
aren't needed for routine application functionality. Normally, one can
create user-defined rules with appropriate object permissions and control
access via role membership.
What application requirement do you have that currently necessitates
db_owner role membership? Perhaps we can come up with an alternative
solution.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mickey Red" <mred@.red.com> wrote in message
news:Xns949B50872244AMRed@.216.196.97.131...
> Hi
> If I create a database and give to the database user
> dbo role membership he is able to change size of datafiles
> mdf and ldf. How can i suppress that so that this is no longer
> possible?
> Kind regards
> Mickey|||Hi Dan
Thanks for your reply.
It's not possible the remove the user from the dbo_owner role because
it's a app developer and needs dbo rights for creating tables and other
objects in a database. The role datawriter is not enough.
I'm still searching for alternatives, because i think all what has to do
with space-management on the server is the dba's job. Perheaps you or
someone other knows an other alternative.
Thanks
Mickey
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
news:#ekYQVH$DHA.2432@.TK2MSFTNGP11.phx.gbl:
> Don't add the user to the db_owner role :-)
> Membership of db_owner, sysadmin, etc. have powerful rights that
> (hopefully) aren't needed for routine application functionality.
> Normally, one can create user-defined rules with appropriate object
> permissions and control access via role membership.
> What application requirement do you have that currently necessitates
> db_owner role membership? Perhaps we can come up with an alternative
> solution.
>|||If your developers need permission to create objects owned by other users,
you can add them to the db_ddladmin and db_securityadmin fixed database
roles. Like db_owner, they can create dbo-owned objects and grant
permissions on them but will not be able to alter the database. However,
with db_securityadmin they could also add themselves to db_owner so it
depends how much you trust your developers.
Another technique is to just grant them CREATE permissions for the
appropriate object types. Newly created objects will be initially owned by
the developer's userid and you can later change the object owner to the one
desired using sp_changeobjectowner.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mickey Red" <mred@.red.com> wrote in message
news:Xns949BE03CEC0B1MRed@.216.196.97.131...
> Hi Dan
> Thanks for your reply.
> It's not possible the remove the user from the dbo_owner role because
> it's a app developer and needs dbo rights for creating tables and other
> objects in a database. The role datawriter is not enough.
> I'm still searching for alternatives, because i think all what has to do
> with space-management on the server is the dba's job. Perheaps you or
> someone other knows an other alternative.
> Thanks
> Mickey
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
> news:#ekYQVH$DHA.2432@.TK2MSFTNGP11.phx.gbl:
>
>|||Hi Dan
Thanks for your reply. I will either try one of the two techniques
you mentioned. Probably it would be the second. Because I don't do
not trust to much the developers.
Kind regards
Mickey
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in
news:OYpmz0T$DHA.3712@.tk2msftngp13.phx.gbl:
> If your developers need permission to create objects owned by other
> users, you can add them to the db_ddladmin and db_securityadmin fixed
> database roles. Like db_owner, they can create dbo-owned objects and
> grant permissions on them but will not be able to alter the database.
> However, with db_securityadmin they could also add themselves to
> db_owner so it depends how much you trust your developers.
> Another technique is to just grant them CREATE permissions for the
> appropriate object types. Newly created objects will be initially
> owned by the developer's userid and you can later change the object
> owner to the one desired using sp_changeobjectowner.
>
