• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • Last Modified:

Database users gone after restore on different server

I am working on a database consolidation project and just migrated the first DB using a backup/restore process. I then noticed that the users section of that DB was empty and users could not connect to the DB. I checked it through Ent. Manager on the server itself and the users were there but it wouldnt show on my client system through Ent. Manager. I deleted and readded the users and now it works and all users can connect again. I did a test with another DB and the same thing happened. What could be causing this? Thanks in advance for any help.
0
acompani
Asked:
acompani
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
You have to "re-sync" the userid in the db with the login on the server.  This is done using command "EXEC sp_change_users_login".

Run the statement below in Query Analyzer and it will generate the change commands you need; then copy and run them in QA also.


SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + usr.name + ''', ''' + usr.name + ''''
FROM sysusers usr WITH (NOLOCK)
LEFT OUTER JOIN master.dbo.syslogins [log] WITH (NOLOCK) ON usr.sid = [log].sid
WHERE usr.hasDbAccess > 0 AND usr.isNtName = 0 AND [log].sid IS NULL
ORDER BY usr.name
0
 
Scott PletcherSenior DBACommented:
The userids aren't so much gone as orphaned.

For example, before running any other commands, go into QA in that db and issue this command:

EXEC sp_helpuser

You should see all the users, but the LoginName column is NULL; that prevents SQL from recognizing them.  The change command updates that column to the correct value.
0
 
acompaniAuthor Commented:
The result of the initial query was a column called 'No Column Name' with no value. I ran it against Master and recieved an EXEC sp_helpuser command for the guest account but nothing for DB I moved over. Thanks for your help.
0
 
acompaniAuthor Commented:
I went ahead and did:

USE DBname
EXEC sp_changedbowner 'sa'
GO

It then mapped sa to the db owner orphaned login.

Thanks for your help, it got me what I needed.
0
 
Scott PletcherSenior DBACommented:
D'OH, yeah, I should have mentioned that the commands above need to be run from within the specific user db, not from master db.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now