Error 21776 when accessing sa account

I get the 21776 error when I try to view the sa account under
security/logins. I can click past it and still view and change account
details, but how do I get rid of the error. I have read about
out-of-sync users problem and have tried the suggested script:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'sa'
EXEC sp_droplogin 'TempOwner'
GO
However I don't know what to use for MyDatabase. Since I'm changing sa
account details I thought that I should point to master. You can't
change the owner of master. I'm a noob. What am I missing. I'm using
msde2000.
Tom
Error 21776: [SQL-DMO]The name 'dbo' was not found in the Users
Collection. If the name is a qualified name, use [] to seperate various
parts of the name, and try again.The error means that the owner of one or more of your user databases is
out-of-sync, probably due to a restore or attach.
You need to run sp_changedbowner in the context of your user database.
sp_changedbowner was corrected in SP3 so you don't need to temporarily
change ownership to a non-conflicting login.
The script below will change ownership for all user databases to 'sa':
DECLARE @.ChangeDBOwner nvarchar(4000)
DECLARE ChangeDBOwner CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT N'USE ' + CATALOG_NAME + N'
EXEC sp_changedbowner ''sa'''
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE CATALOG_NAME NOT IN
(
'master',
'model',
'msdb',
'tempdb',
'distribution'
)
OPEN ChangeDBOwner
WHILE 1 = 1
BEGIN
FETCH NEXT FROM ChangeDBOwner INTO @.ChangeDBOwner
IF @.@.FETCH_STATUS = -1 BREAK
EXEC(@.ChangeDBOwner)
END
CLOSE ChangeDBOwner
DEALLOCATE ChangeDBOwner
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
--
"Tom Wells" <noemail@.nowhere.none> wrote in message
news:%23kHB4HWeDHA.3160@.tk2msftngp13.phx.gbl...
> I get the 21776 error when I try to view the sa account under
> security/logins. I can click past it and still view and change account
> details, but how do I get rid of the error. I have read about
> out-of-sync users problem and have tried the suggested script:
> USE MyDatabase
> EXEC sp_addlogin 'TempOwner'
> EXEC sp_changedbowner 'TempOwner'
> EXEC sp_changedbowner 'sa'
> EXEC sp_droplogin 'TempOwner'
> GO
> However I don't know what to use for MyDatabase. Since I'm changing sa
> account details I thought that I should point to master. You can't
> change the owner of master. I'm a noob. What am I missing. I'm using
> msde2000.
> Tom
> Error 21776: [SQL-DMO]The name 'dbo' was not found in the Users
> Collection. If the name is a qualified name, use [] to seperate
various
> parts of the name, and try again.
>|||I wish I understood better what I was doing but it worked. I used the
Query Analyzer and ran the script against each of my user databases.
After running the script the sa user showed up as the dbo of the database.
Thanks
Tom
Dan Guzman wrote:
> The error means that the owner of one or more of your user databases is
> out-of-sync, probably due to a restore or attach.
> You need to run sp_changedbowner in the context of your user database.
> sp_changedbowner was corrected in SP3 so you don't need to temporarily
> change ownership to a non-conflicting login.
> The script below will change ownership for all user databases to 'sa':
> DECLARE @.ChangeDBOwner nvarchar(4000)
> DECLARE ChangeDBOwner CURSOR
> LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT N'USE ' + CATALOG_NAME + N'
> EXEC sp_changedbowner ''sa'''
> FROM INFORMATION_SCHEMA.SCHEMATA
> WHERE CATALOG_NAME NOT IN
> (
> 'master',
> 'model',
> 'msdb',
> 'tempdb',
> 'distribution'
> )
> OPEN ChangeDBOwner
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM ChangeDBOwner INTO @.ChangeDBOwner
> IF @.@.FETCH_STATUS = -1 BREAK
> EXEC(@.ChangeDBOwner)
> END
> CLOSE ChangeDBOwner
> DEALLOCATE ChangeDBOwner
> GO
>|||The database owner SID is stored in 2 separate places - sysdatabases
(master database) and the 'dbo' row in the sysusers (user database).
The GUI tools misbehave when these get out of sync due to a restore or
attach. The sp_changedbowner script corrected the mismatch.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tom Wells" <noemail@.nowhere.none> wrote in message
news:euuMXy5eDHA.1056@.TK2MSFTNGP10.phx.gbl...
> I wish I understood better what I was doing but it worked. I used the
> Query Analyzer and ran the script against each of my user databases.
> After running the script the sa user showed up as the dbo of the
database.
> Thanks
> Tom
> Dan Guzman wrote:
> > The error means that the owner of one or more of your user databases
is
> > out-of-sync, probably due to a restore or attach.
> >
> > You need to run sp_changedbowner in the context of your user
database.
> > sp_changedbowner was corrected in SP3 so you don't need to
temporarily
> > change ownership to a non-conflicting login.
> >
> > The script below will change ownership for all user databases to
'sa':
> >
> > DECLARE @.ChangeDBOwner nvarchar(4000)
> > DECLARE ChangeDBOwner CURSOR
> > LOCAL FAST_FORWARD READ_ONLY FOR
> > SELECT N'USE ' + CATALOG_NAME + N'
> > EXEC sp_changedbowner ''sa'''
> > FROM INFORMATION_SCHEMA.SCHEMATA
> > WHERE CATALOG_NAME NOT IN
> > (
> > 'master',
> > 'model',
> > 'msdb',
> > 'tempdb',
> > 'distribution'
> > )
> > OPEN ChangeDBOwner
> > WHILE 1 = 1
> > BEGIN
> > FETCH NEXT FROM ChangeDBOwner INTO @.ChangeDBOwner
> > IF @.@.FETCH_STATUS = -1 BREAK
> > EXEC(@.ChangeDBOwner)
> > END
> > CLOSE ChangeDBOwner
> > DEALLOCATE ChangeDBOwner
> > GO
> >
>