dbo login account

Question

dbo login account

CTracy

SSC Eights!

Points: 903
More actions
January 27, 2006 at 10:36 am

#170736

We have a software application that came delievered with two different logins. domain\support and support. Within the actual database they show up as:
name:dbo; login:domain\support
name:support; login:support
Ones an Active directory account, the other is a sql login.
For security I want to replace the user support with something else...we'll call this account admin.
My concern is the account name:dbo; login:domain\support. In order to make sure this software functions properly I'm thinking the new account should be called name:dbo; login domain\admin. If I try to make any changes to the name:dbo; login domain\support I get an error: "Error 15405: Cannont use the reserved user or role name 'dbo'."
Any suggestions on my options here? How should I go about remapping that dbo user to the new active directory login?
Thanks,
-c

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

DBO is a reserved word for DataBase Owner. It cannot be a user created login or role and cannot be mapped in the way you want to do. You can assign a login to the dbo role.

-SQLBill

CTracy SSC Eights! Points: 903 More actions · Answer 2

That's always been my impression. But however they setup the software there is a user named dbo with a login of domain\support. Now...it does not show up like that if you view the logins for the server in EM. It's name is simply domain\support. But in each database it's the other way. I was thinking they may have used a command something like: sp_grantdbaccess 'domain\support', 'dbo'. But that's won't work because, like you said, 'dbo' is a reserved word.

Any ideas of how they did this?

-c

CTracy SSC Eights! Points: 903 More actions · Answer 3

This is what I came up with:

--Allow modifications of system tables

exec sp_configure 'allow updates',1

go

reconfigure with override

--Change the old db user to the new one

update sysusers set sid =0x01050000000000051500000027497463930396536E34DF1251440000 where uid = 1

--put things back to the way they started

exec sp_configure 'allow updates',0

go

reconfigure with override

I then had to detach and reatach the db...or I probably could have stoped and started the server or taken the db offline then back on. But the user then showed up as dbo with the new account.

Hope it works ok.

K. Brian Kelley SSC Guru Points: 114602 More actions · Answer 4

There are two supported ways to have a login show up as the dbo user. Neither require you to update the sysusers table.

The first, and simplest, is simply to make the login the owner of the database. If the login is already mapped as a user in the database, verify the user doesn't own any objects in said database. It doesn't sound like that's the case but it's always something to check. Once you've verified, revoke access to the database for that user. There is a method to this madness and I'll explain.

The login that owns the database will map to the user account dbo. However, if the login is already mapped to a user, then trying to make it an owner of the database will fail. Therefore, you have to get the login completely out of the database. Once that is accomplished, making sure you're in the database, run sp_changedbowner and specify the login in question. For instance, for Northwind you would do the following:

USE Northwind
GO

EXEC sp_changedbowner 'MyDomain\MyLogin'
GO

That's the recommended method. The second method involves setting an alias, and that's done by using sp_addalias. It too requires the login to be cleared out of the database. However, sp_addalias is considered deprecated... it's a hold over from SQL Server 6.5. The alias won't show up properly (like a user would) in Enterprise Manager. Therefore, it is generally recommended against.

K. Brian Kelley
@kbriankelley

CTracy SSC Eights! Points: 903 More actions · Answer 5

Thanks for your help. Sorry this post is a little late but I've been away for a couple days.

So I tried your method and had trouble from the start. I am unable to revoke db_owner role or even delete the user. When I try to delete the user I receive the message: "The selected user cannot be dropped because the user owns objects.

All the objects in this database are owned by dbo. Do you see anything wrong with the method I was using in my post above. Where I edit the system table. It appears to have worked.

Thanks,

-c

CTracy SSC Eights! Points: 903 More actions · Answer 6

Never mind...it does work. I just used sp_changedbowner and all is happy. Thanks for your help.

-c