October 7, 2013 at 8:16 am
Hi
I need to re-create the logins on my old server onto my new server. I wish to use a method that transfers the users password and BUT ALSO keeps their current database mappings and permissions. I keep getting forwarded to the following link http://support.microsoft.com/kb/918992 which creates the user ok (still got to test if it keeps the current password) but it does not map the users database permissions and I am having to manually go in and add this, which is a pain. Is there a way to script this task?
October 7, 2013 at 12:39 pm
are you transferring the databases as well, because this should retain the login\user mappings. Please describe your full process.
---------------------------------------------------------------------
October 7, 2013 at 1:32 pm
When you backup databases from source and restore them in the destination server, all logins are restored with the databases. After that run the microsoft script to transfer logins, http://support.microsoft.com/kb/918992. The script creates the logins in the destination servers, all passwords are moved too.
October 7, 2013 at 1:34 pm
This is how you transfer logins with SQL 2012, I would try it to see if it works. Pretty cool!
http://support.microsoft.com/kb/918992
MCSE SQL Server 2012\2014\2016
October 7, 2013 at 1:36 pm
It works with 2008 as well, I used it.
October 7, 2013 at 2:29 pm
are you transferring the databases as well, because this should retain the login\user mappings. Please describe your full process.
No i have created a new database. I looking for a method to script the existing logins. The links that i have suggested do not copy the users mappings - it just re creates the login with the password and i have to then manually add the database mappings, which is a time consuming process.
October 7, 2013 at 2:31 pm
It works with 2008 as well, I used it.
Are you sure that it also mapped the users database access and permissions ? When i run the stored procedure in that link i just get the user login and password re created - not the database mappings
October 7, 2013 at 2:56 pm
that script only gives the logins, sids and passwords, plus default database.
The user permissions would only come across if you copied the databases over. As you are not you need to either use the database copy wizard (I wouldn't) or here is a script to get the user permissions out of the source database
http://www.sqlservercentral.com/scripts/Permissions/76450/
you might need to check if the logins have any server level roles assigned as well, and also default language
---------------------------------------------------------------------
October 8, 2013 at 2:12 am
PearlJammer1 (10/7/2013)
I wish to use a method that transfers the users password and BUT ALSO keeps their current database mappings and permissions.
When we restore a backup to new location . the users get orphan on that new location .So we need to fix those orphan users.
Search on google for " sql server 2008 + fix orphan user" , you will get multiple links./scripts.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 8, 2013 at 6:40 am
All user permissions gets copied over when you backup/restore. I have migrated many databases, and found that best way to backup/restore and run the microsfot script to copy all users. To check for orphands, type below:
use database
go
EXEC sp_change_users_login 'Report'
If above showed any record, then you need to fix it by running:
EXEC sp_change_users_login 'Update_One', 'usernamefromabovequery', 'usernamefromabovequery'
I am sure there are better ways out there, but that is how I go about migrating databases along with their permissions.
October 8, 2013 at 7:06 am
PearlJammer1 (10/7/2013)
It works with 2008 as well, I used it.
Are you sure that it also mapped the users database access and permissions ? When i run the stored procedure in that link i just get the user login and password re created - not the database mappings
It does bring over mappings with 2012. I used it when I moved a DN from 08 to 12. Everything was the same.
MCSE SQL Server 2012\2014\2016
October 8, 2013 at 7:33 am
It's the Database!!! (10/8/2013)
PearlJammer1 (10/7/2013)
It works with 2008 as well, I used it.
Are you sure that it also mapped the users database access and permissions ? When i run the stored procedure in that link i just get the user login and password re created - not the database mappings
It does bring over mappings with 2012. I used it when I moved a DN from 08 to 12. Everything was the same.
still looks like the same old revlogin to me, no way thats going to bring over permissions from within user databases. As the OP is not bringing over his databases he needs a separate method to get user information (as opposed to logins).
Information on how to fix orphaned logins will not help here.
---------------------------------------------------------------------
October 8, 2013 at 2:39 pm
george sibbald (10/8/2013)
It's the Database!!! (10/8/2013)
PearlJammer1 (10/7/2013)
It works with 2008 as well, I used it.
Are you sure that it also mapped the users database access and permissions ? When i run the stored procedure in that link i just get the user login and password re created - not the database mappings
It does bring over mappings with 2012. I used it when I moved a DN from 08 to 12. Everything was the same.
still looks like the same old revlogin to me, no way thats going to bring over permissions from within user databases. As the OP is not bringing over his databases he needs a separate method to get user information (as opposed to logins).
Information on how to fix orphaned logins will not help here.
It does, I promise. I am in a alwayson config and I have to bring over everything including the SID. It works trust me! 🙂
MCSE SQL Server 2012\2014\2016
October 9, 2013 at 11:36 am
Hi, what do you mean by mapping? Yes that script will tie the logins to the users in the databases because it brings the sids over, i.e. they are mapped , but it will not of itself create the permissions in the user database.
Those are normally bought over when you restore the database and logins and users then tie together, in your case this was done when you set up the the availability group and the databases were copied over.
Pearljammer is not bringing over the databases so needs a separate step to do that.
---------------------------------------------------------------------
October 9, 2013 at 12:18 pm
Let me break this down for you;
1. Backup DB on stand alone server.
2. Restore DB on Primary node (AlwaysOn)
3. Run the create script I get from running the SP_revlogin
4. Users are mapped, SIDS same and permissions are there
Yes database permissions come over with the backup, they are orphaned until you run the script to re-mapped under the server login options and they are no longer orphaned.
Either way it will work. I use to use the sp_change SP before 2012 came out. It was a suggestion for you to work around. My stuff works, it's a preference the way I see it. :):-D
MCSE SQL Server 2012\2014\2016
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply