Copy user permissions from database to named instance

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
LVL 52
Dale FyeOwner, Developing Solutions LLCAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Deepak ChauhanSQL Server DBACommented:
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, Developing Solutions LLCAuthor Commented:
Client has not had a chance to attempt this yet. Will get back to this next week.
Dale FyeOwner, Developing Solutions LLCAuthor Commented:
thanks for your help, guys.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.