Link to home
Start Free TrialLog in
Avatar of String :-)
String :-)Flag for Australia

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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

You can take backup and restore the back somewhere else. There you will get everything including users.

There is not way you can view password of other people.
Avatar of 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?
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_here>',  
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.
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

--

Open in new window


--
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? :-)
I am assuming, by the way, that you aren't using contained databases.
ASKER CERTIFIED SOLUTION
Avatar of Zberteoc
Zberteoc
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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