dbo puzzle

Hi,

I managed to get things broken and I can't solve it:

Scenario:
SQL Server
>Databases
>TestDB
> Users
> dbo mapped to user1 (user1 = SQL Server user)
> user1(database user) mapped to nothing (no SQL user)
->Security
-> Logins
-> user1

Problem: I don't want SQL Server user 'user1' to be dbo but I can't remove dbo rights because it owns objects nor can I drop database user 'user1' or delete SQL Server user 'user1' because it's mapped to dbo.

I'm running in circles..
First and last time I use the auto_fix option to fix orphaned users.
At this point, the only solution to me is dropping the database.

Anyone with a better idea?

Thank you!
Best Regards,
Worf

Have you tried these steps, in this order?

1. Create another user, User2

2. Add User2 to db_creator role

3. Transfer ownership of each object owned by User1 to User2

4. Remove dbo rights from User1

5. Remove User1 from db_owner role

6. Remove User1 from sysadmin role

7. Transfer ownership of each object owned by user2 to dbo

8. Remove User2 from db_creator role

9. Remove User2

10. Remove User1 if desired.

|||

which version of sql server is this? i believe SQL Server 2005. in that case this user1 might be owner of schema User1 and USER1 schema is the owner of objects. what need to do is change the owner of schema by

ALTER AUTHORIZATION ON SCHEMA::[User1] TO [dbo or whichever schema u want to transfer]

then drop the user

Madhu

Dbo access does not work.

I have a domain user account, DOM\User1, who I have granted dbo rights to
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin rights
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallenI apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
--
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Seems to me like you have done some DENY in the database, or possibly added to db_denydatareader and
db_denydatawriter to the db_owner role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uPjPeOAlGHA.408@.TK2MSFTNGP03.phx.gbl...
> Raymond Lewallen wrote:
>> I have a domain user account, DOM\User1, who I have granted dbo rights to DatabaseA, which is on
>> a server who is a member of the domain DOM as well. User1 can add, remove, alter tables and
>> stored procedures etc, but when User1 attempts to update, select, insert or delete a row from any
>> table in DatabaseA, even if it is a table User1 just created, the user is given a
>> Select/Update/Insert/Delete Permission Denied error, depending on the task.
>> The only way to get past the problem is to give DOM\User1 system admin rights on the server.
>> If I create a Sql Server user, UserSql1, and give that user dbo access to DatabaseA and attempt
>> to Select/Update/Insert/Delete, then it works just fine for UserSql1. Its only the domain
>> accounts that do not work correctly.
>> Any ideas on this?
>> Raymond Lewallen
>> http://www.codebetter.com/blogs/raymond.lewallen
> Have you explicitly DENIED access to any particular domain groups? Does DOM\User1 belong to one
> of those groups?
>