Manage security principals

In this tutorial we will learn how to manage security principals.

However, Azure SQL Managed Instance provides nearly all security features that the latest SQL Server (Enterprise Edition) database engine has:

Firstly, limit access in an isolated environment
Secondly, use authentication mechanisms that require identity: Azure Active Directory (Azure AD) and SQL Authentication
Thirdly, use authorization with role-based memberships and permissions
Lastly, enable security features

Create an Azure AD server principal (login) using SSMS

The first Azure AD server principal (login) can be created by the standard SQL admin account (non-Azure AD) that is a sysadmin, or the Azure AD admin for the managed instance created during the provisioning process.

Firstly, log into your managed instance using a standard SQL login account (non-Azure AD) that is a sysadmin or an Azure AD admin for SQL Managed Instance, using SQL Server Management Studio.
Secondly, in Object Explorer, right-click the server and choose New Query.
Thirdly, in the query window, use the following syntax to create a login for a local Azure AD account:

SQL
USE master
GO
CREATE LOGIN login_name FROM EXTERNAL PROVIDER
GO

SQL
USE master
GO
CREATE LOGIN [[email protected]] FROM EXTERNAL PROVIDER
GO

Then, on the toolbar, select Execute to create the login.
Lastly, check the newly added login, by executing the following T-SQL command:

SQL
SELECT *
FROM sys.server_principals;
GO

Grant permissions to create logins

To create other Azure AD server principals (logins), SQL Server roles or permissions must be granted to the principal (SQL or Azure AD).

SQL authentication

If the login is a SQL principal, only logins that are part of the sysadmin role can use the create command to create logins for an Azure AD account.

Azure AD authentication

Firstly, to allow the newly created Azure AD server principal (login) the ability to create other logins for other Azure AD users, groups, or applications, grant the login sysadmin or securityadmin server role.
Secondly, at a minimum, ALTER ANY LOGIN permission must be granted to the Azure AD server principal (login) to create other Azure AD server principals (logins).
Thirdly, by default, the standard permission granted to newly created Azure AD server principals (logins) in master is: CONNECT SQL and VIEW ANY DATABASE.
Lastly, the sysadmin server role can be granted to many Azure AD server principals (logins) within a managed instance.

To add the login to the sysadmin server role:

Firstly, log into the managed instance again, or use the existing connection with the Azure AD admin or SQL principal that is a sysadmin.
Secondly, in Object Explorer, right-click the server and choose New Query.
Lastly, grant the Azure AD server principal (login) the sysadmin server role by using the following T-SQL syntax:

SQL
ALTER SERVER ROLE sysadmin ADD MEMBER login_name
GO

SQL
ALTER SERVER ROLE sysadmin ADD MEMBER [[email protected]]GO

Create an Azure AD user from the Azure AD server principal (login)

Authorization to individual databases works much in the same way in SQL Managed Instance as it does with databases in SQL Server. However, a user can be created from an existing login in a database, and be provided with permissions on that database, or added to a database role.

Now that we’ve created a database called MyMITestDB, and a login that only has default permissions, the next step is to create a user from that login. At the moment, the login can connect to the managed instance, and see all the databases, but can’t interact with the databases. Further, if you sign in with the Azure AD account that has the default permissions, and try to expand the newly created database, you’ll see the following error:

Create an Azure AD user and create a sample table

Firstly, log into your managed instance using a sysadmin account using SQL Server Management Studio.
Secondly, in Object Explorer, right-click the server and choose New Query.
Thirdly, in the query window, use the following syntax to create an Azure AD user from an Azure AD server principal (login):

SQL
USE — provide your database name
GO
CREATE USER user_name FROM LOGIN login_name
GO

Next, it’s also supported to create an Azure AD user from an Azure AD server principal (login) that is a group. The following example creates a login for the Azure AD group mygroup that exists in your Azure AD instance.

SQL
USE MyMITestDB
GO
CREATE USER [mygroup] FROM LOGIN [mygroup]GO

After that, in a new query window, create a test table using the following T-SQL command:

SQL
USE MyMITestDB
GO
CREATE TABLE TestTable
(
AccountNum varchar(10),
City varchar(255),
Name varchar(255),
State varchar(2)
);

Next, create a connection in SSMS with the user that was created. You’ll notice that you cannot see the table TestTable that was created by the sysadmin earlier. We need to provide the user with permissions to read data from the database.
Lastly, you can check the current permission the user has by executing the following command:

SQL
SELECT * FROM sys.fn_my_permissions(‘MyMITestDB’,’DATABASE’)
GO

Add users to database-level roles

For the user to see data in the database, we can provide database-level roles to the user.

Firstly, log into your managed instance using a sysadmin account using SQL Server Management Studio.
Secondly, in Object Explorer, right-click the server and choose New Query.
Thirdly, grant the Azure AD user the db_datareader database role by using the following T-SQL syntax:

SQL
Use — provide your database name
ALTER ROLE db_datareader ADD MEMBER user_name
GO

Next, check the Azure AD user that was created in the database exists by executing the following command:

SQL
SELECT * FROM sys.database_principals
GO

After that, create a new connection to the managed instance with the user that has been added to the db_datareader role.
Now, expand the database in Object Explorer to see the table.
Lastly, open a new query window and execute the following SELECT statement:

SQL
SELECT *
FROM TestTable

Use cross-database queries

Cross-database queries are supported for Azure AD accounts with Azure AD server principals (logins). However,to test a cross-database query with an Azure AD group, we need to create another database and table.

Firstly, log into your managed instance using a sysadmin account using SQL Server Management Studio.
Secondly, in Object Explorer, right-click the server and choose New Query.
Thirdly, in the query window, use the following command to create a database named MyMITestDB2 and table named TestTable2:

SQL
CREATE DATABASE MyMITestDB2;
GO
USE MyMITestDB2
GO
CREATE TABLE TestTable2
(
EmpId varchar(10),
FirstName varchar(255),
LastName varchar(255),
Status varchar(10)
);

Next, in a new query window, execute the following command to create the user mygroup in the new database MyMITestDB2, and grant SELECT permissions on that database to mygroup:

SQL
USE MyMITestDB2
GO
CREATE USER [mygroup] FROM LOGIN [mygroup]GO
GRANT SELECT TO [mygroup]GO

After that, sign into the managed instance using SQL Server Management Studio as a member of the Azure AD group mygroup. Open a new query window and execute the cross-database SELECT statement:

SQL
USE MyMITestDB
SELECT * FROM MyMITestDB2..TestTable2
GO