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?
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?
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial