Migrating Microsoft SQL Server Logins
After importation your databases if you’re unable to attach mistreatment your SQL login, you will receive the error ‘Login unsuccessful for user ‘example.’ (Microsoft SQL Server, Error: 18456).‘ as a result of the info is within the ancient Login and User Model, logins square measure hold on on an individual basis within the supply server and credentials aren’t contained among the info itself.
From now on, the destination server may be organized to use the Contained info User Model that keeps the logins in your info and out of the supply server. (You will scan additional concerning this here). Until then, we’ll need to move and move with the users as a part of the standard model.
Backing up and restoring the databases did move your SQL logins relevance the databases (your logins square measure still related to the proper databases with the proper permissions) however the particular logins itself didn’t transfer to the new server.
You’ll verify this by gap SSMS on the destination server and navigating to Server > Security > Logins. You’ll notice that any custom SQL logins you created on the previous server didn’t transfer up here, however if you move to Server > infos > Your Database (AdventureWorks2012 during this case) > Security > Users you’ll see the proper login related to the info.
If you have got one or 2 SQL users, you’ll simply delete the user’s association to the info in Servers > Databases > AdventureWorks2012 > Security > Users, re-create the user in Server > Security > Logins and map it to the right info.
If you have got plenty of logins, you’ll need to follow an extra method made public below. To migrate all SQL users, open a brand new question window on the supply server and run the subsequent script:
This script creates two stored procedures in the source database which helps with migrating these logins. Open a New Query window and run the following:
This query outputs a script that creates new logins for the destination server. Copy the output of this query and save it for later. You will need to run this on the destination server.
Once you’ve copied the output of this query, login to SSMS on the destination server and open a New Query window. Paste the contents from the previous script (it should have a series of lines that look similar to — Login: BUILTIN\Administrators
CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE = [master]) and hit Execute.
You have now successfully imported all SQL logins. Now verify that the databases have been migrated to the destination server by using your previous credentials.
Migrating Views and Stored Procedures
- Open Microsoft SQL Management Studio on the Source server.
- Log in to your SQL server.
- Expand the server and as well as Databases.
- Right click on the name of your database and go to Tasks > Generate Scripts.
- Click Next.
- We will change Script entire database and all database objects to Select specific database objects and only check Views and Stored Procedures.
- Click Next, notice the Save to File option. Take note of the file path listed. In my case, it is C:\Users\Administrator\Documents\script.sql – The path of saved views and stored procedures.
- Click Next >> Next >>Finish, and select C:\Users\Administrator\Documents\script.sql and copy it to the destination server.
- Go to the destination server, open SSMS and log in to the SQL server.
- Go to File > Open > File or use the keyboard shortcut CTRL+O to open the SQL script. Select the file C:\Users\Administrator\Documents\script.sql to open it.
- You will see the script generated from the source server containing all views and stored procedures. Click Execute or use the keyboard shortcut F5 and run the script.
Unfortunately, there is no built-in way to do this with the command line. There are 3rd party tools and even a tool by Microsoft called mssql-scripterfor more advanced scripting.
You have now migrated the views and stored procedures to your destination server! Repeat this process for each database you are migrating. A little guidance goes a long way in database administration. Every SQL server will have its own configurations and obstacles to face but we hope this article has given you a strong foundation for your Microsoft SQL Server Migration.