Changing SA passwords

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.

Reader Feedback

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 tdichiara@techtarget.com 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.