Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I setup a SQL Server job to Backup and Validate all my DB's

After doing so, in most of my databases the user is no longer listed. The user is there, but not associated.

What would cause this?

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Are you restoring the database on a different server instance? Database users (listed under Security at the database level) are not the same as server instance logins (listed under Security at the server level). If you restore a database to a different instance then typically the database users will be disassociated since the server instance logins don't exist on the target instance. Even if you create a login on the target instance with the same name as on the oringal instance it's not going to work because the new login with have a different security identifier (SID).

You can copy logins between server instances using the sp_help_revlogin stored procedure. This will generate a script that will create the login using the same SID, password, everything. Run the script on the target server and the database users will once again be associated with the server logins.

Source code and more information on how to use sp_help_revlogin can be found on here.

share|improve this answer

Was the database restored to a different server? If that's the case, then the SIDs for any SQL Server logins will be different on that server. Database users are mapped to logins based on the SID, so the users end up being orphaned.

To update a user to match the existing login, use the following query:

ALTER USER username WITH LOGIN = loginname

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.