Hello,

Let me start off by stating I am not a seasoned DBA. I have been given a project to copy everything (SQL databases, SSIS Packages, SQL Jobs, Logins, Server Roles assigned to users, etc). Basically set up a new SQL Server so that I can decommission the old SQL Server. I think I have everything copied over except for the SQL logins. I was able to copy over the users with Windows (Active directory) logins by simply choosing Script login as; CREATE To; New Query Editor Window on the source SQL server and then copy, paste and run the query on the destination server. When I follow this same method on the SQL logins the query is created as below:

/****** Object:  Login [MyDataBase]    Script Date: 09/08/2015 11:50:40 ******/

/* For security reasons the login is created disabled and with a random password. */

/****** Object:  Login [MyDataBase]    Script Date: 09/08/2015 11:50:40 ******/

CREATE LOGIN [MyDataBase] WITH PASSWORD=N'G4~)m_u131 ?_6;$)K5_w_h|', DEFAULT_DATABASE=[MyDataBase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

ALTER LOGIN [MyDataBase] DISABLE

My question is; if I create the SQL login using the script above it will generate a random password. How can I change this password if it is random and I dont know what it is?

If I go into the properties of a SQL login I see the Password and Confirm password fields are populated. The person who set this up no longer works at my instution so I dont know which password he created for the SQL logins.

How do I copy a SQL login from the source SQL server to the new destination SQL server?

Also, When I finally do copy over the SQL logins will there be an issue with the SIDs? If yes, what will that effect?

The source SQL server is SQL Server 2005 SP2 standard edition 32 bit. The OS is Windows Server 2003 R2 SP2. The destination SQL server is SQL Server 2012 Developer Edition x64 and the OS is Windows Server 2012 R2 Standard.

Any assistance will be greatly appreciated. Thank you.

Dave

Dave,

For login transfer what you have to do is use the revlogin script which helps you to transfer logins along with their passwords

https://support.microsoft.com/en-us/kb/2461

Once your migration is done make sure you run an Orphan report

sp_change_users_login 'report' and eliminate if any unless the source DB is a contained Db(2012) which is not in you

You can run any one of the below:

1.Run MS standard SP for Tx logins

  EXEC master..sp_help_revlogin

  Run above SP in old system (sql 2005) and will get the output with sql logins/pw's.Then then run this output into your target server.

OR
2.You can run this link script.
SynchSQLServerLogins

Thanks

• Edited by SQLDBA321 13 hours 4 minutes ago

Hello,

Please try the methods explained on the following Microsoft Support article:

https://support.microsoft.com/en-us/kb/918992

Hope this helps.


Regards,

Alberto Morillo
SQLCoffee.com