Re: dbo association

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 08/18/03 

Date: Mon, 18 Aug 2003 12:23:12 -0500

The dbo user is a special case; database ownership determines the login
mapping for the dbo user. You can change database ownership with
sp_changedbowner:

Use MyDatabase
EXEC sp_changedbowner 'MyLogin'
GO

In some cases, you may get ambiguous message 'the proposed owner is
already a user in the database'. You can workaround the error by
temporarily changing ownership to a non-conflicting login:

Use MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'MyLogin'
EXEC sp_droplogin 'TempOwner'
GO 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
-----------------------
SQL FAQ links (courtesy  Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------
"Mark" <mar.mcgaughey@milestonelp.com> wrote in message
news:0aa701c365a6$36da7990$a601280a@phx.gbl...
> I have restored a data base backup from a different sql
> server that has different users "dbo" for instance. How
> can I associate "dbo" in the security login with one that
> is created in the database table that was restored? Will
> it matter? I have tried to use the following procedure but
> will not work for any member of the security group.
> sp_change_users_login [ @Action = ] 'action'
>     [ , [ @UserNamePattern = ] 'user' ]
>     [ , [ @LoginName = ] 'login' ]
>
> auto_fix, Update_one will not work either.
>
> Thank you for your help

Relevant Pages

  • Re: users with no logins
    ... User login mapping can get out-of-sync following a restore. ... an error stating 'the proposed owner is already a user in the database' ... EXEC sp_changedbowner 'TempLogin' ... the dbo user name shows "sa" as the login. ...
    (microsoft.public.sqlserver.security)
  • Re: Import Logins from text or spreadsheet
    ... set psw to generic value ... add user to database ... EXEC sp_addrolemember 'SomeRole', 'SomeLogin' ... Let's assume your Excel spreadsheet has 3 columns: Login, ...
    (microsoft.public.sqlserver.server)
  • Re: Import Logins from text or spreadsheet
    ... Good suggestion, Steve. ... EXEC sp_addlogin $, $, $(Database) ... EXEC sp_addrolemember $, $(Login) ...
    (microsoft.public.sqlserver.server)
  • Re: Using osql.exe to set account permissions?
    ... To accomplish mentioned tasks one may do the following: ... EXEC sp_defaultdb 'Domain\UserName', 'box_LM_W3SVC_1_Collab' ... > Create a New Login ... > Permit database access for that login to that database ...
    (microsoft.public.sqlserver.security)
  • Re: users with no logins
    ... > User login mapping can get out-of-sync following a restore. ... > an error stating 'the proposed owner is already a user in the database' ... EXEC sp_changedbowner 'TempLogin' ...
    (microsoft.public.sqlserver.security)