Re: Change dbo
- From: "GoodwinM" <mitchgoodwin@xxxxxxxxxxx>
- Date: Sat, 5 Apr 2008 09:22:41 -0400
"GoodwinM" <mitchgoodwin@xxxxxxxxxxx> wrote in message
news:%23PAKyuxlIHA.1368@xxxxxxxxxxxxxxxxxxxxxxx
"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9A7782DE3BF14Yazorman@xxxxxxxxxxxx
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
Let me elaborate on the issue.
I think I am at your item 1
Setup:MS Office Accounting 2008 Professional (MSOA) on SQL Server Express
A new company was created under a user account A, I think on another
machine.
This database was then detached, moved to the server, attached.
Within MSOA you assign users to specific roles to limit which accounting
documents they can see. We now want to limit what user A can do within the
database. It appears however, that since user A created the original
datbase, he has full permissions to do anything and if you attempt to add
him, from within the application, it indicates he is already a member of a
group that has permissions. So, I cannot add user A, but user A, by way
of something, has full permissions. I should note that I can add and
change the permissions of other users from within the application without
issue.
If I look at things via SQL Server Management Studio this is what I see:
From the database: Security/Users and select 'dbo', it has the user name
of dbo, a login name of 'domain\A' and this cannot be changed.
I am thinking that since this appears to connect user A to dbo, this is
why user A is able to do whatever he wants in the company database.
From the server: Security/Logins/A/User Mappings - for the database in
question, under 'Users Mapped to this Login', it indicates the user name
is dbo, and the default schema is also dbo. If I uncheck the box 'Map',
then click on OK, I get the error: Cannot Drop User 'dbo'.
OK, I have used your suggestion, "ALTER AUTHORIZATION ON DATABASE::thisdb
TO someoneelse".
I first attempted to change to user 'Administrator', this failed with
message about already there or an alias.
I then added a new user and ran the same query to authorize the newly
created user and that worked.
I would rather use Administrator, but thanks to you, at least user A no
longer has full permissions. Thank you very much.
.
- Follow-Ups:
- Re: Change dbo
- From: Erland Sommarskog
- Re: Change dbo
- References:
- Change dbo
- From: GoodwinM
- Re: Change dbo
- From: Erland Sommarskog
- Re: Change dbo
- From: GoodwinM
- Change dbo
- Prev by Date: Re: Change dbo
- Next by Date: Re: Change dbo
- Previous by thread: Re: Change dbo
- Next by thread: Re: Change dbo
- Index(es):
Relevant Pages
|
