Copy user permissions from database to named instance

Dale Fye
Dale Fye used Ask the Experts™
on
A client recently created a new named instance on his SQL Server and migrated a database from another server to that new named instance.  When he did so, users were carried over from the old server/database to the new database when that database was restored, but the instance level logins were not copied over, and when he tries to create the user in the named instance, he gets a message indicating that the user already exists.

Does he need to remove each of the database users and add them at the instance level, and then assign them to the database?  or is there a way to script those users from original SQL Server database and create them in the named instance?database vs instance security
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
If they are domain users, and you're in the same domain, they should automatically sync back up.  Since that didn't happen, I guess it's a different domain.  If so, yes, I'd delete the existing loginsand users and create new ones.

For native SQL logins on the same domain, you just need to issue this command in each db for each native SQL login:

EXEC sp_change_users_login 'UPDATE_ONE', 'user_name', 'login_name'

Typically user_name and login_name will be the same, but they don't have to be.
Commented:
Whenever you restore a backup to another server in same domain or another domain all sql native users will be orphan because respective login not exists or parent SID (Login SID) mismatched on the destinatin server. To avaoid this situation we follow the procedure menitoned in the link. This way you can copy the same SID with existing hashed password.

https://support.microsoft.com/en-us/kb/918992


Otherwise for Windows or AD user no need to worry just create a windows login into destination server and user is already exists in the database.

But for SQL native user you have to create login with password for each user and remap the login

Step 1. create sql login with password
step 2. use <database>
             execute sp_change_users_login 'report'
this will show you all sql native orphan user. copy these users
step 3. execute sp_change_users_login 'Auto_fix', <'usrname'>
repeat this exercise for all orphan users.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Client has not had a chance to attempt this yet. Will get back to this next week.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
thanks for your help, guys.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial