Re: dbo role and modify filesize of database

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 08:22:57 -0600

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:
>
> >> 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.
> >
>

Relevant Pages

  • Permissions for database
    ... This is a common problem. ... For each database, create a developer role and a Readonly ... The developers can be ... should have select permissions and execute permissions. ...
    (microsoft.public.sqlserver.security)
  • Re: Restrict access to single db from members of server administrator role
    ... Sysadmin role members have full permissions and you can't deny/revoke ... It seems to me that your support database belongs on the 'live' ... server rather than the development one. ... developers full access to the development instance while protecting your ...
    (microsoft.public.sqlserver.security)
  • SQL Server Role Security
    ... I have a group of developers that I want to allow permissions to ... create/alter table structures in a particular database. ... them rights to alter the database itself just rights to alter the structures ... DevAdmin is a Windows 2K Group containing the developers. ...
    (microsoft.public.sqlserver.security)
  • Re: List Users Permissions down to table.column action
    ... THIS STORED PROCEDURE GENERATES COMMANDS ... -- FIXED PROBLEMS WITH STATEMENT LEVEL PERMISSIONS GRANTING. ... -- CREATE TABLE TO HOLD LIST OF USERS IN CURRENT DATABASE ... -- GRANT USER ACCESS TO SERVER ROLES ...
    (microsoft.public.sqlserver.security)
  • Re: User access on a company intranet
    ... Yes they need full permissions on the folder where the backend is. ... You wouldn't need to do this in your copy of the database. ... However you can toggle the shiftkey bypass from another mdb file. ... When you want to implement security, you create a new mdw file, ...
    (microsoft.public.access.security)