SQL 2012 - Database Move to new server - Unknown SQL User password

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.

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

--

Select allOpen 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.

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