Moving Users to a New Server in MS SQL – Quick Ideas

At times youtransfer-hosting_11178.JPG may need to change the server running a SQL Server instance in your company. Now irrespective of whether you are looking to upgrade to a new machine or even move to a new location, you would have to migrate all SQL users too. Now when mention that users need to be moved, we are referring to usernames and passwords that need to be transferred to the new machine. Now this can also be achieved manually but the process is tedious and ideally should be avoided unless you have very few users. Let’s look at some quick ways to complete the transfer process.

 

Consider Using T-SQL

Creating a script to achieve the transfer process is often one of the easiest ways migrate users between different SQL instances. In fact the Microsoft support site, knowledgebase article 918992, provides you with a readymade script.

 

Use the Copy Database Wizard option

Another quick way to transfer the logins involves using the copy database wizard from the SQL Server Management Studio. The process is outright simple and you should just follow the onscreen instructions offered by the wizard to select the objects that you wish to move.

 

Take recourse to SQL Server Integration Service

One of the easiest ways to complete the transfer process involves using SSIS service to transfer the system identifiers in a seamless manner through the transfer login task. When you access the Transfer login task editor you need to mark the CopySids option as true. Using the edition you can quickly choose the specific logins you wish to move or even choose all existing logins too.

 

Possible Complications

When you use any of the above approaches, you should normally be able to successfully move all your users. However in certain cases some complications may arise. The most common one relates to users that are not shifted correctly causing an error message when that login is used on the new server. If you do encounter such a scenario, you need to first identify the user and then map it correctly using the Alter User function in T-SQL.

 

SQL Crashes can at times lead to a data loss if the right steps are not taken

While MS SQL Server has built in functions to recover your database after a crash, often the repair process may land into roadblocks. Ideally the best way to approach a SQL crash involves the use of a superb sql recovery tool like the top notch DataNumen SQL Recovery to get back your missing data. From batch processing to handling an array of media types, the application is rich in features and versatility. The tool can even tackle a large SQL file with equal ease and is capable of recovering NDF files. With the application getting added to the context menu in Windows, the tool can be accessed directly from any folder. For advanced users, the tool even allows operation through the command line.

 

Author Introduction:

Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit https://www.datanumen.com/

Comments are closed.