Ever try and change your SA password only to get an error like this: "Error 21776: [SQL-DMO] The name 'dbo' was not found in the Users collection. If the name is a qualified name, use  to separate various parts of the name, and try again."
Yes, we've probably all seen it. You can still change the password by using sp_password but that doesn't fix your problem. This error, from my experience, occurs when you DTS or restore a database from one server to another. If you look in the users collection you will not see the SA account.
To fix this error, run sp_changedbowner 'sa' on each database that does not have SA as a user. NOTE: You may have to go into each user collection and refresh to see the account. I have had to use this at one time or another on almost every SQL server I administer (12). Hope this helps someone out there.
Jaime M. writes: "I thought this tip was very helpful. Many DBAs do not actually check the default settings for the SA password."
Richard B. writes: "Mr. Higdon's tip was very helpful. Thanks!
For More Information
- What do you think about this tip? E-mail the Editor at firstname.lastname@example.org with your feedback.
- The Best SQL Server Web Links: tips, tutorials, scripts, and more.
- Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.