I received the same error message when trying to connect to an SDE that I restored from a backup. I successfully connected once using the following steps in SQL Server Management Studio:
Under Security > Users - delete the user
Add a the user back to the database from the list of SQL users and provide the same Database role membership
Detatch the database
Re-attach the database
I have not been able to reproduce this success on subsequent restored backups.
Anybody have an answer to this?