Restoring Server User IDs

Check your hard copy of syslogins and your restored syslogins table.

  • If you have added server logins since the last backup of master, reissue the create login commands.

  • If you have dropped server logins, reissue the drop login commands.

  • If you have locked server accounts, reissue the sp_locklogin commands.

  • Check for other differences caused by the use of alter login by users or by system administrators.

Make sure that the suids assigned to users are correct. Mismatched suid values in databases can lead to permission problems, and users may not be able to access tables or run commands.

An effective technique for checking existing suid values is to perform a union on each sysusers table in your user databases. You can include master in this procedure, if users have permission to use master.

For example:
select suid, name from master..sysusers
select suid, name from sales..sysusers
select suid, name from parts..sysusers
select suid, name from accounting..sysusers

If your resulting list shows skipped suid values in the range where you were restoring logins, add placeholders for the skipped values and then drop them with drop login or lock them with sp_locklogin.