Don't panic

 

Cry about...
SQL Server Troubleshooting


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


Symptom:

When attempting to add a user (or modify a user’s properties) the following error is produced:

Error 21776: [SQL-DMO] The name 'XXXX' 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.

Where ‘XXXX’ is the name of the account that is being created or having its properties viewed.

An agent has the status ‘Never started’ and whilst it can be told to start it doesn't run and the status never change from ‘Never started’. This can affect Snapshot Agents, Log Reader Agents, Distribution Agents, Merge Agents or Miscellaneous Agents.

Cause:

Not known, but may be in some way a consequence of having copied the database from another server.

Possible Remedies:

  • See the Microsoft knowledge base article Q218172 (Problem: Cannot Change SA Password in Enterprise Manager). That article covers the same error but with a different cause and solution.
  • Try deleting user account and then recreating it. Do this with SQL rather than via Enterprise Manger. The following sequence cured the problem on the server where it was observed (although it is not clear at which point the problem went away):

    use MASTER
    go

    exec sp_revokedbaccess 'ACCOUNT'
    exec sp_revokelogin 'DOMAIN\ACCOUNT'
    go

    use DATABASE
    go

    exec sp_revokedbaccess 'ACCOUNT'
    exec sp_revokelogin 'DOMAIN\ACCOUNT'
    exec sp_grant login 'DOMAIN\ACCOUNT'
    exec sp_defaultdb 'DOMAIN\ACCOUNT','DATABASE'
    exec sp_grantdbaccess 'DOMAIN\ACCOUNT', 'ACCOUNT'
    go

    where ‘ACCOUNT’ is the name of the user account, ‘DOMAIN’ is the name of the NT domain in which the account is defined and and ‘DATABASE’ is the name of the database that the user is to have access.


These notes are believed to be correct for SQL Server 7 and may apply to other versions as well.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.