Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Migrate SQL Server Logins with PowerShell


By:   |   Last Updated: 2017-01-31   |   Comments (1)   |   Related Tips: More > Security

Problem

As a DBA we often have a need to migrate SQL Server logins, permissions, server roles, etc. from one server to another. There are various approaches like using sp_help_revlogin, SSIS, Copy Database Wizard, etc. In this tip we will cover a new approach using PowerShell.

Solution

As there are various approaches to migrate logins, the most commonly used approach is using the Microsoft stored procedure sp_help_revlogin. Below are the high level steps involved when using sp_help_revlogin.

  • Execute the stored procedure sp_help_revlogin on source server and save the output.
  • Execute the output script on the destination server

Once we have executed the script on the destination server, we will have all logins with their passwords and SIDs, but here are some issues with this approach.

  • We may have different versions of sp_help_revlogin on the source and destination servers.
  • The login server roles, server permissions are not transferred.
  • Database roles, permissions are also missing on the destination server.

So it becomes difficult and time consuming to take care of all of the necessary components.

Migrate SQL Server Logins Between Instances using PowerShell

PowerShell makes a DBA's life easier by simplifying the tasks. We can easily migrate between instances of SQL Server by downloading and installing the PowerShell DBA Tools.  After it has been downloaded, run it on Windows PowerShell to install as shown below.

Migrate SQL Server Logins Between Instances using PowerShell

Once installed, we can find modules related to logins with the below command.

Get-Command -Name *login*

Find modules related to logins

The two we are going to cover are Copy-SqlLogin and Export-SqlLogin which are new PowerShell functions to ease the task of login migrations.

Copy-SqlLogin uses PowerShell and SMO to migrate SQL logins including their: SIDs, passwords, server/database roles, server/database permission sets & securables, default database and login attributes.

Export-SqlLogin is useful to export SQL logins, server/database roles, permissions, and login properties.

Migrating SQL Server Logins with Copy-SqlLogin

We will create some sample data and then show how this can be used.

I am creating three logins in my SQL Server 2014 (default) instance and we will use Copy-SqlLogin to migrate these to a SQL Server 2016 (SQL2016) instance with all permissions and properties.  These are run on the SQL Server 2014 default instance.

Create DemoLogin1

USE [master]
GO
CREATE LOGIN [DemoLogin1] WITH PASSWORD=N'DemoLogin1', DEFAULT_DATABASE=[msdb], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
ALTER SERVER ROLE [dbcreator] ADD MEMBER [DemoLogin1]
GO
USE [msdb]
GO
CREATE USER [DemoLogin1] FOR LOGIN [DemoLogin1]
GO
USE [msdb]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DemoLogin1]
GO
USE [msdb]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [DemoLogin1]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentOperatorRole] ADD MEMBER [DemoLogin1]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [DemoLogin1]
GO

Create DemoLogin2

USE [master]
GO

CREATE LOGIN [DemoLogin2] WITH PASSWORD=N'DemoLogin2', DEFAULT_DATABASE=[AdventureWorks2014], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

ALTER SERVER ROLE [processadmin] ADD MEMBER [DemoLogin2]
GO
USE [AdventureWorks2014]
GO
CREATE USER [DemoLogin2] FOR LOGIN [DemoLogin2]
GO
USE [AdventureWorks2014]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DemoLogin2]
GO
USE [master]
GO
CREATE USER [DemoLogin2] FOR LOGIN [DemoLogin2]
GO
USE [master]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DemoLogin2]
GO
USE [ReportServer]
GO
CREATE USER [DemoLogin2] FOR LOGIN [DemoLogin2]
GO
USE [ReportServer]
GO
ALTER ROLE [db_datareader] ADD MEMBER [DemoLogin2]
GO

Create DemoLogin3

USE [master]
GO
CREATE LOGIN [DemoLogin3] WITH PASSWORD=N'[email protected]', DEFAULT_DATABASE=[TEST], DEFAULT_LANGUAGE=[French], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [setupadmin] ADD MEMBER [DemoLogin3]
GO
USE [ABCD]
GO
CREATE USER [DemoLogin3] FOR LOGIN [DemoLogin3]
GO
USE [ABCD]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [DemoLogin3]
GO

Before we move on let's check the properties of Copy-SqlLogin by running the following:

get-help copy-sqllogin

check the properties of Copy-SqlLogin

We can see the following in the description:

  • For SQL Server 2000 it migrates logins with SID, password, server roles and database roles.
  • For SQL Server 2005 and above it migrate logins with SID, password, defaultdb, server roles & securables, database permissions & securables, and login attribute.
  • Since the login hash algorithm changed in SQL Server 2012 and is not backwards compatible with previous versions of SQL Server, SQL Server 2000 logins can be transferred to SQL Server 2012 or later, but not vice-versa.

Suppose I want to transfer Demologin1 from SQL Server 2014 to SQL Server 2016, we need can run the below code in PowerShell.

Copy-SqlLogin -Source localhost -Destination localhost\sql2016 -Logins DemoLogin1

transfer Demologin1 from SQL Server 2014 to SQL Server 2016

We can see in the output section it gives details during the transfer of the login and permissions. In my case it also issues a warning that the SQL Server 2016 instance is not configured with mixed authentication, so we need to enable mixed authentication after the migration.

If the login is already present on the destination server, it gives a warning and also suggests using the -Force parameter.

login is already present on the destination server, so warning suggests using the -Force parameter

If we want to drop and recreate the login at the destination server, specify the -Force parameter as shown below.

drop and recreate the login at the destination server, specify the -Force parameter

We can see in the output above that by using the -Force parameter it is attempting to drop the login on the destination instance.

To migrate multiple logins at a time specify them altogether as shown below.

Copy-SqlLogin -Source localhost -Destination localhost\sql2016   -Logins DemoLogin2,DemoLogin3

This will migrate the two logins to the SQL Server 2016 instance.

migrate multiple logins at a time specify them altogether

Below we can compare the login DemoLogin3 on the source and destination instances after the migration.

compare the login DemoLogin3 on the source and destination instances after the migration

compare the login DemoLogin3 on the source and destination instances for the Login Properties

We can see that the logins properties, server roles are similar on both instances. Since database 'test' (the logins default) is not present on SQL Server 2016 it is mapped to the master database. This information can be seen in the output above.

We can migrate both SQL and Windows logins to the destination server. To migrate Windows logins, specify the login as domain\user as shown below.

Copy-SqlLogin -Source localhost -Destination localhost\sql2016 -Logins DemoLogin2,DemoLogin3,'Domain\user'

To migrate all users from one instance to another, remove the -Logins parameter from the scripts as follows.

Copy-SqlLogin -Source localhost -Destination localhost\sql2016  

If we want to exclude a specific login, use the -Exclude parameter as shown below.

 Copy-SqlLogin -Source localhost -Destination localhost\sql2016  -Exclude Demologin1

Exporting SQL Server Logins with Export-SqlLogin

As specified earlier, Export-SqlLogin is used to export the Windows and SQL logins into a .sql file This export includes the login, SID, password, default database, default language, server permissions, server roles, database permissions, and database roles.

Below is help information about Export-SqlLogin.

Exporting SQL Server Logins with Export-SqlLogin

Suppose we want to export just DemoLogin2 into file C:\temp\demologin2.sql, the command is as follows.

 Export-SqlLogin -Source localhost  -login Demologin2 -filepath 'c:\temp\demologin2.sql'

export just DemoLogin2 into file C:\temp\demologin2.sql

We can see in the file demologin2.sql that it contains the login script with all permissions.

the file demologin2.sql that it contains the login script with all permissions

Note: By default, all logins with the exception of system logins (####Example Login## and local (SERVERNAME\administrators) are copied.

Next Steps


Last Updated: 2017-01-31


next webcast button


next tip button



About the author
MSSQLTips author Rajendra Gupta Rajendra is a Consultant DBA with 9+ years of extensive experience in database administration including large critical OLAP, OLTP, Reporting and SharePoint databases.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, January 11, 2019 - 3:57:31 PM - Jon Duarte Back To Top

Are there any limitations in terms of sources and destinations?

We are wanting to go from 2005 to 2017. Would this process work??


Learn more about SQL Server tools