Error 21776

Hi,
l do a full backup of the database to a another server every evening with no problems but l keep getting this error message for a particular user the next morning " 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 ". l have to delete the user account adnd recreate it every morning, is there a script that automates this process for me please.

Any help, it's urgent.

Thanks,

TBOY7.Hi,
l do a full backup of the database to a another server every evening with no problems but l keep getting this error message for a particular user the next morning " 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 ". l have to delete the user account adnd recreate it every morning, is there a script that automates this process for me please.

Any help, it's urgent.

Thanks,

TBOY7.

If I am understanding your question correctly, you are taking the backup of a database made on Server A and restoring it to a database on a different Server B.

If this is not the case, stop reading. The rest of my answer is predicated on this scenario. If I am wrong, please restate your problem and clarify.

The reason for your error is that the SID for the login (note the careful use of the term login) on Server A is different from the SID for the same login on Server B. When you restore the backup to Server B, the SID for the user in the database being restored is not found and therefore the user is unlinked from the login.

To resolve this issue, look for the stored procedure sp_change_users_login in SQL Books On Line. I recommend AGAINST using the 'AUTO_FIX' option, but that's just me.

Regards,

hmscott

PS. A "login" is a server level entity. A "user" is a database level entity. A "login" is mapped to a "user" using T-SQL.