Re: Change dbo

GoodwinM (mitchgoodwin@xxxxxxxxxxx) writes:
I am not a SQL kind of person, but sure hope someone here is:

I have a database where a user is defined as the dbo on a database. I do
not want this user defined as such. I assume this was generated during
creation of the database.

Is there a way to remove this user from being dbo on a database, or
changing the dbo schema to another user? I tried using SQL Server Manger
and just unchecking the box, but of course this fails.

Permit me to first some terminology straight. In SQL Server we talk
about server *logins* and database *users*. In a database, the login is
mapped to a database user. In many cases, the name of the user is the
same as the name of the login, but it does not have to be that way.
And there is one important exception: the owner of the database always
map to the user dbo, and so does members of the sysadmin server role.

So strictly speaking, your question is contracdictive. The user dbo is
dbo by definition, and this cannot be changed. But apparently that is
not what you are asking. My little problem is that I have to guess a bit,
as there are two possible interpretations of what you are really asking
for:

1) The login for this person maps to dbo.
2) The login for this person does not map to dbo, but the login has
db_owner rights in the database.

In the first case, this could be because that the login is the owner of
the database. In this case change.

ALTER AUTHORIZATION ON DATABASE::thisdb TO someoneelse

You may then need to map this login to a new user in the database, so
that this person still have access to the database.

It may also be because the login is a member of the sysadmin role, in
which case you should drop him from that role with sp_dropserverrolemember.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Exchange of login for database user
    ... A database must be owned by someone. ... > Cause last time the user "dbo" is not tied to any login. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Confused about dbo
    ... neither the explicit database owner or aliased to the dbo user. ... member of the db_owner role does not mean you are aliased to the dbo user. ... When you connect to SQL Server you do so using a login. ...
    (microsoft.public.sqlserver.security)