We are happy to announce a public preview for T-SQL DDL support for Windows domain users migration from SQL Server on-premises databases to SQL managed instance (MI) with Azure AD authentication.
The migration process for databases from SQL Server on-premises to MI can be achieved in different ways. However, for SQL on-premises databases that contain Windows users/groups, two main mechanisms are proposed:
Prerequisites
The following prerequisites apply:
The new DDL syntax support for migration
Once the database originating from SQL Server on-premises is restored on MI, the following T-SQL DDL commands allow users to remap Windows users and groups to Azure AD users and groups.
For Windows users with logins:
For Windows groups with logins:
For Windows users without logins (contained users):
For Windows groups w/o logins:
In all the above cases [UserName@Domain.com] and [GroupName] must exist in Azure AD. This means that the synchronization between the on-premises Windows Domain and Azure Active Directory has to be done beforehand.
Results
Below are the Windows users existing in the database after being migrated to MI and before running the ALTER USER command (upper screenshot) and after running the ALTER USER command (lower screenshot), where the migrated Windows users are changed to the new Azure AD users with their new SIDs (see query results below).
Permissions and Role-membership migration
All Permissions and database-role memberships will be retained for the migrated database users.
However, if the logins on the SQL Server on-premises had been granted server scoped permissions or roles, those are not retained by these steps as they are contained in the master-database. In order to migrate those, you can either use DMS for the migration, or revert to T-SQL scripts.
For more information on this topic, including a step-by-step tutorial, please see:
Tutorial: Migrating SQL Server on-premises Windows users and groups to Azure SQL Database managed in... and ALTER USER (Transact-SQL).
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.