hi thereI get the following error

[nat35]

hi thereI get the following error when attempting to change the SA password:

Error 21776: [SQL-DMO] The name 'DBO' was not found in the Users collection...
thank you

 

[osjohnm]

Hi

Have you recently restored a database onto this server from another one? This is happens because:

Quote from Microsoft

"This problem can be the result of performing a restore to a server where the login that was mapped to the database owner (DBO) on the source server does not exist on the destination server. In this case, one or more DBOs are mapped to NULL rather than to a specific login. The SQL Server Distributed Management Object (SQL-DMO) collection will have no record of a login for 'DBO' and will not allow the SA password change."

To check run the following against the restored database, if you receive 'dbo is unmatched' then execute sp_changedbowner below against the database.

SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"

FROM sysusers u

LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid

WHERE u.name = 'dbo'

-----

exec sp_changedbowner 'sa'


John

 

[nat35]

Hi John,
Yes the database has been restored recently.
I run the script above agains database and name is dbo and
matched login sa
when i run sp_changedbowner i have error:
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 46
The proposed new database owner is already a user in the database.
I am still not able to change password
How do I determine what databases have an owner that does not exist?
thank you

 

[osjohnm]

Hi

Run the script against each database that you have on that server until you find the correct one.

Another possibility is if a user who accesses the sql server via Windows groups or Windows authentication and creates a database that user won't match any entry in the syslogins table.

Here is the link to the Microsoft article about the first scenario:

http://support.microsoft.com/default.aspx?scid=kb;en-us;218172

Here is the link to the Microsoft article where I found the script when I had this problem a while ago and covers the NT Authentication scenario:

http://support.microsoft.com/default.aspx?scid=kb;en-us;305711

As I mentioned run the script against all databases and as the article points out. It is one of these scenarios and since the "restored" db isn't the cause then it is probably one of the other databases. THe script should find it for you.

Once you have found the db run sp_changedbowner and you'll be one your way.

Hope this helps

John

 

[nat35]

thank you John,
I have not been attended work for week.
I'll try now.
nat