Lesson Learned #35: How to transfer the login and user from OnPremise to Azure SQL Database

[Jose_Manuel_Jurado]

First published on MSDN on Mar 29, 2018
In some situations, we need to transfer the logins/user or contained user from our OnPremise Server to Azure SQL Database.

In previous version of SQL Server, running the script included in this URL the process will be successfully, but trying to execute into Azure SQL Database you will face the following errors at the step 2:

• Msg 262, Level 14, State 18, Procedure sp_hexadecimal, Line 1 [Batch Start Line 5] CREATE PROCEDURE permission denied in database 'master'.


• Msg 262, Level 14, State 18, Procedure sp_help_revlogin, Line 1 [Batch Start Line 37] CREATE PROCEDURE permission denied in database 'master'.

Unfortunately, this script is not supported to use in Azure SQL Database, due to several differentials in the engine. Even the Windows users will be not supported in Azure SQL Database, just only SQL Logins and Azure Active Directory. Also, some other properties of the users like DEFAULT_LANGUAGE or DEFAULT_DATABASE is not supported.

Also, there is very important to know that Azure SQL Database currently, doesn't support Windows Logins, so, if you need to export your database to Azure SQL Database, please, follow these steps:

• Create a new database from a backup of your database


• Remove the Windows Logins


• Create the export


• And import the database.

As we cannot use a native format backup, you need to generate a bacpac that will have the login, password and user.

• If you are not using a contained database, if you try to create the user directly, for example, using Generate script/Export Data from SQL Server Management Studio, you are going to have the following scenario, when the user will be created.

CREATE USER [User1] FOR LOGIN [User1] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [User1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[Id] [int] NOT NULL,
[Name] [varchar](20) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

• So, you just only to create the login before executing the script: CREATE LOGIN [User1] WITH PASSWORD=’<PASSWORD123>’


• The SID is not possible to transfer between OnPremise environment to Azure SQL Database. For example, if I try to execute the following I will have the following error:

CREATE LOGIN [User1]
WITH PASSWORD=N'8AHisc8cRB5dBpDaOuA7irbHP4CD',
SID = 0x54E338B637F93B4BAF54855EE70

In another hand, if you are transfering logins from Azure SQL Database to Azure SQL Database. In this case we have 4 different scenarios:

• If you have created the login, review this link Geo-Replication


• If you have contained users, there is not needed to do anything.


• If you have the login and transferred the data using bacpac, there is not needed to do anything.


• If you have the login and the transferred of the data has been used using CREATE DATABASE .. AS COPY OF from different servers, you need to do the following the same page that geo-replication