String :-)
asked on
SQL 2012 - Database Move to new server - Unknown SQL User password
Hi all,
Ive been asked to move a DB to a new server.
Current and new SQL servers are running Server2012R2, both members of the domain, and both have identical versions SQL versions 11.0.5613.
The DB has 2 x local SQL users - And you guessed it, no one knows the passwords for these accounts.
Is there any way of showing these passwords, or exporting\importing the SQL users from one SQL Server to another?.
Many thanks,
String
Ive been asked to move a DB to a new server.
Current and new SQL servers are running Server2012R2, both members of the domain, and both have identical versions SQL versions 11.0.5613.
The DB has 2 x local SQL users - And you guessed it, no one knows the passwords for these accounts.
Is there any way of showing these passwords, or exporting\importing the SQL users from one SQL Server to another?.
Many thanks,
String
ASKER
Hi Pawan, Thanks for taking time to comment on my problem.
I have tried backup and restore of database on new server however that leaves orphan'ed users in the DB.
ie
DB references a SQL user that new server doesnt know about.
Any tips?
I have tried backup and restore of database on new server however that leaves orphan'ed users in the DB.
ie
DB references a SQL user that new server doesnt know about.
Any tips?
Boss
Here is what you need..
Resolve an Orphaned User
In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:
CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_he re>',
SID = <SID>;
To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.
ALTER USER <user_name> WITH Login = <login_name>;
When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.
ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere> ';
System_CAPS_ICON_important .jpg Important
Any login can change it's own password. Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. However, only members of the sysadmin role can modify passwords of sysadmin role members.
https://msdn.microsoft.com/en-IN/library/ms175475.aspx
Pls let me know if you need more help on this.
Here is what you need..
Resolve an Orphaned User
In the master database, use the CREATE LOGIN statement with the SID option to recreate a missing login, providing the SID of the database user obtained in the previous section:
CREATE LOGIN <login_name>
WITH PASSWORD = '<use_a_strong_password_he
SID = <SID>;
To map an orphaned user to a login which already exists in master, execute the ALTER USER statement in the user database, specifying the login name.
ALTER USER <user_name> WITH Login = <login_name>;
When you recreate a missing login, the user can access the database using the password provided. Then the user can alter the password of the login account by using the ALTER LOGIN statement.
ALTER LOGIN <login_name> WITH PASSWORD = '<enterStrongPasswordHere>
System_CAPS_ICON_important
Any login can change it's own password. Only logins with the ALTER ANY LOGIN permission can change the password of another user's login. However, only members of the sysadmin role can modify passwords of sysadmin role members.
https://msdn.microsoft.com/en-IN/library/ms175475.aspx
Pls let me know if you need more help on this.
Sample Code..
--
--
--
--
USE DBNAME;
GO
CREATE LOGIN sqlUser
WITH PASSWORD = 'P@ssword', DEFAULT_DATABASE = DBNAME;
GO
CREATE USER sqlUser
FOR LOGIN sqlUser;
GO
DROP LOGIN sqlUser;
CREATE LOGIN sqlUser
WITH PASSWORD = 'P@ssword', DEFAULT_DATABASE = DBNAME;
GO
--
--
You should be able to back up and restore the master database along with the application database(s), which is where all those logins and passwords are stored. It has been many years since I last did that, and I don't know what limitations there may be today, but as long as the versions are exactly the same it should be possible. It might be good to keep the file paths the same as well -- something is trying to come back to me about that but it hasn't so far.
When I did this, when I reached the 'restore' part I followed the steps from a TechNet article like this one. Here is another take on the subject.
You could still have orphaned users in the application databases afterward, but you can reconnect them to their logins using sp_change_users_login. I normally use the 'update_one' option, but auto_fix might be useful in this situation.
I believe I have also, in the distant past, managed to restore msdb, so as to not have to set up all the jobs again. model can be restored too. I used an article like this as a guide.
Have you considered virtualization? :-)
When I did this, when I reached the 'restore' part I followed the steps from a TechNet article like this one. Here is another take on the subject.
You could still have orphaned users in the application databases afterward, but you can reconnect them to their logins using sp_change_users_login. I normally use the 'update_one' option, but auto_fix might be useful in this situation.
I believe I have also, in the distant past, managed to restore msdb, so as to not have to set up all the jobs again. model can be restored too. I used an article like this as a guide.
Have you considered virtualization? :-)
I am assuming, by the way, that you aren't using contained databases.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Any feedback about this question? Please check the answers.
The DB has 2 x local SQL users - And you guessed it, no one knows the passwords for these accounts.Are the users being used from an application? If so the application has those passwords stored and you just need to find if it's encrypted or not.
ASKER
Hi all,
This migration all went well.
I used Zberteoc's script which ran well and achieved the result. I did research what the script did and how to use it before running it.
More background info referenced here.
Method 1
https://support.microsoft.com/en-au/kb/246133
Many thanks to all who contributed.
String
This migration all went well.
I used Zberteoc's script which ran well and achieved the result. I did research what the script did and how to use it before running it.
More background info referenced here.
Method 1
https://support.microsoft.com/en-au/kb/246133
Many thanks to all who contributed.
String
There is not way you can view password of other people.