SQL Server 2000General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.

Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .

I forgot my sa password and was using normal user account to open EM for a long time. Now I wanted to login to EM or Query analyser using SA, since BULK INSERT is not allowed for logins other than sysadmin role.

I tried from EM, to use WINDOWS NT Authentication, and tried changing the password for sa, and was getting an error as follows.

"Error 21776: [SQL-DMO]Then 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"

I checked for dbo in sysusers table and it exists there. Not sure what it means or what I should do now to change the password for sa.

I was trying to change the password for sa from the EM, by editing properties of sa.

Not sure what you 've done really,
your best bet is to try to unregister your sql server off your enterprise manager and reregister it, and use Windows authentication model. If that won't work, unfortunately, you're heading for a master db backup, however, you will need to restore the system dbs (msdb,model) and your working dbs, because this means your server will be cleaned.

-- edited --

Also from a website I found this :

Quote:

quote:
To reset the sa password, you can make the following:

1. Login to the SQL Server box as the Administrator.
2. Run SQL Server Enterprise Manager.
3. Right-click the server name and choose 'Edit SQL Server Registration properties'.
4. Choose 'Use Windows authentication' and click OK button.
5. Expand a server, expand a Security and click Logins.
6. Double-click the sa login and specify new password on the General tab.

I unregistered and re-registered the sql server from EM to use windows authentication this time again, but no luck. Still when I tried to change the sa password, I get the same error I posted in the first post.

Yes, you are right. I am already able to connect to query analyser using windows authentication. But I am not allowed to login as administrator from anywhere or no one else is allowed to use this administrator account. On request, on a temporary basis I have been allowed to use administrator account till I resolve the sa password issue. So my concern is to somehow change the sa password and take off from there.

Try to use directly the sp_password stored procedure from Query Analyzer. Maybe it will work this way. (I took a look at the code EM generates when you try to change a password, and it uses sp_password).

When I tried using sp_password with different OLDPASSWORDs, I got the password changed for the SA.

You made me remember my old password somehow. Actually I tried changing with the both the old and new passwords as same word, and it said the password was changed. To my wonder I have tried the same password manytimes earlier and failed to login. Not sure what went wrong. But now I am able to login using sa.

Thanks a ton. For everyone who tried their hands on this. You all really got pulled out of this.