Re: properties owner and users owner.
From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 08/19/04
- Next message: Dan D.: "moving a database"
- Previous message: Leo: "SQL Database LOG resstore"
- In reply to: ben brugman: "Re: properties owner and users owner."
- Next in thread: Uri Dimant: "Re: properties owner and users owner."
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 19 Aug 2004 08:09:35 -0500
'dbo' is a special database user and must exist in every database. The
database owner is the *login* that is mapped to the database 'dbo' user and
this mapping is stored in 2 places, sysdatabases and sysusers. These should
normally be the same login but can get out-of-sync in some situations, such
as a restore or attach. The query below will return the same login if the
owner entries are synchronized:
USE MyDatabase
SELECT 'sysdatabases mapping=' + SUSER_SNAME(sid)
FROM master..sysdatabases
WHERE name = DB_NAME()
UNION ALL
SELECT 'sysusers mapping=' + SUSER_SNAME(sid)
FROM sysusers
WHERE name = 'dbo'
You can execute sp_changedbowner to change or correct the mapping. If you
get error 15110, ensure the specified login is not already a database user
since a login can be mapped to only one database user at a time.
The 15110 error can also be raised due to an out-of-sync condition mentioned
above. In that case, temporarily change database ownership to a
non-conflicting login and then to the desired login like the example below:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'MyDatabaseOwner'
EXEC sp_droplogin 'TempOwner'
GO
-- Hope this helps. Dan Guzman SQL Server MVP "ben brugman" <ben@niethier.nl> wrote in message news:%23nqiINdhEHA.3076@tk2msftngp13.phx.gbl... > Thanks for your time and quick response, > see inline : > > "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message > news:O2$NJIdhEHA.2052@tk2msftngp13.phx.gbl... > > Hi, > > > > "Both can be same in most of the situation." > > I tried to make both the same. > > > > > DBO - DBO is a role, A user with a DBO role can do any activities inside > > the database. > > > > Properties section of a database there is a owner? > > > > He will be person who creates the database. By default he will a DBO. This > > owner can be changed using the procedure, > > > > sp_changedbowner. > > I cannot change the owner because : > " > Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46 > The proposed new database owner is already a user in the database. > " > > And in the database he is the dbo owner, so I do not think deleting that > owner > is wise. > > I still have difficulty grasping the difference between the two owners and > still would like them to be the same. > > Thanks > ben brugman > > > > > Thanks > > Hari > > MCDBA > > > > > > > > > > "ben brugman" <ben@niethier.nl> wrote in message > > news:eCuSx$chEHA.4064@TK2MSFTNGP12.phx.gbl... > > > In the properties section of a database there is a owner. > > > > > > Under the users of a database there is the dbo with > > > a Login Name. > > > > > > What is the difference between the 'two owners' ? > > > > > > ben brugman > > > > > > > > > > > >
- Next message: Dan D.: "moving a database"
- Previous message: Leo: "SQL Database LOG resstore"
- In reply to: ben brugman: "Re: properties owner and users owner."
- Next in thread: Uri Dimant: "Re: properties owner and users owner."
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
