Re: Question about dropping owner's permissions...

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 10/29/03 

Date: Wed, 29 Oct 2003 09:37:53 -0800

Jason

When a member of the db_owner role creates an object, the default owner is
the user name of the creator. I said a db_owner CAN create objects owned by
dbo, must the owner must be specified:

CREATE TABLE dbo.newtable ( )

Regular users in the db (non-db_owners) would get an error when they tried
to create such a table. 

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Jason Barnett" <jbarnett74@hotmail.com> wrote in message
news:e4#Uh5jnDHA.392@TK2MSFTNGP11.phx.gbl...
>     I apologize.  As it turns out, that is precisely what I wanted to do.
> For some reason it didn't seem to be working; when I assigned the user to
> db_owner group, the objects they created were owned by the user -- not
dbo.
> I must have had a conflicting setting somewhere in the process.
>     Thanks for your help.
>
> ---Jason
>
>
>
> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message
> news:%2380fTtjnDHA.2424@TK2MSFTNGP10.phx.gbl...
> > Hi Jason
> >
> > I'm not completely sure I understand the questions. But, why don't you
> have
> > all objects owned by the dbo user. Since there is always a dbo, you
> wouldn't
> > have to switch names when someone leaves. A user in the db_owner role
can
> > create objects owned by the dbo user, and that is what I always
recommend.
> >
> > --
> > HTH
> > ----------------
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Jason Barnett" <jbarnett74@hotmail.com> wrote in message
> > news:#MHzQKjnDHA.2000@TK2MSFTNGP12.phx.gbl...
> > >     I'm using SQL Server 7.0 and my question pertains to securing my
> test
> > > machine so that developers can create objects within databases, but
not
> > > create databases themselves.  I have done this by adding them to the
> > > db_owner role for each database (about 24 of them).  This would work
> fine
> > if
> > > I could easily switch ownership of all objects in the database at the
> time
> > > that the developer leaves the company.
> > >     Can anyone help me reach my end goal: Allowing developers to
create
> > > objects but not databases, and easily switch the owner?
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Permission Denied executing SP that reads foreign table!
    ... >> the creators rights and in this case its the dbo who owns the SP. ... > are not checked as long as the objects involved have the same owner. ... All objects across these databases on the same server are ... > the ownership chain, it is the login associated with the object owner. ...
    (microsoft.public.sqlserver.security)
  • Re: ADP: Cant use stored procedure on remote SQL server
    ... Not only I use dbo everywhere but I ... If you don't mention the owner when creating a new stored procedure, view, ... BTW in the database window, all the stored procedures are followed by ... Check also the owner of the SPInc stored procedure. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: rda push when owner is not dbo.
    ... If I launch the sql query analyzewr and try to run the ... I'm pretty configdent that the owner name not being qualified is the ... > I pull the table with rda to a local table named task. ... > I also created a test table in the same database with owner dbo and I ...
    (microsoft.public.sqlserver.ce)
  • Re: Permissions and ODBC
    ... objects have the same owner. ... case of dbo-owned objects in different databases, ... See the SQL 2003 SP3 Books Online for more ... > The Ms Access program executes a stored procedure that resides in SQL ...
    (microsoft.public.sqlserver.server)
  • Re: User names
    ... I don't have a pre-sp3 server to try this on, but in sp3, if you are a user ... byjust specifically giving the owner name in QA. ... > dbo was the owner. ...
    (microsoft.public.sqlserver.server)