Adding Active Directory users to Azure SQL databases

If you are working with Azure SQL Database you will find adding user a bit different than general SQL DB, especially; adding Active Directory users. Though it's easy and a few steps, off course if you know the steps. While I was working on it and went through different routes so thought about sharing the easy one which worked like Gem.

There are two authentication approach for Azure SQL DB and SQL Managed Instance.

1) SQL Authentication

2) Azure Active directory Authentication

Fig 1.0: Authentication type in Azure

SQL authentication is straight forward, while you are creating database in Azure, you must have to put SQL server credential which will become your Azure database credential if you don't create database credential separately. As long as you have administrative credential to login to the database then you create all necessary users. However, you can't create Active directory users in Azure by logging as SQL authenticate user.

For example, I have logged in with the SQL admin user and then tried to execute below query to add Active directory user:

CREATE USER [user1@domain.com] 

FROM EXTERNAL PROVIDER 

WITH DEFAULT_SCHEMA = dbo;  

  

--add user to role(s) for the particular database

ALTER ROLE dbmanager ADD MEMBER [user1@domain.com]; 

ALTER ROLE loginmanager ADD MEMBER [user1@domain.com]; 

However, found below error:

Fig 2.0: Error creating AD user

The error message is self explanatory: "Principal 'user1@domain.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users."

So, how to solve it? Or How to add the AD user to Azure SQL DB ?

Step 1: Please login to you azure portal and find out your SQL server resource and you will find 'Active directory Admin' left side under settings. Please click 'Active Directory Admin' to find out UI like below where click 'Set admin' , now your Active Directory user account become Admin to the SQL server. 

Fig 3.0:  Active Directory Admin

Step 2: Now either use SSMS or Azure data studio and login with Active directory authentication. Since my organization have Multi factor Authentication (MFA) enables so I have chosen Azure Active Directoty- Universal with MFA, but you can choose either 'Active directory - Integrated' or 'Active Directory - Password.'

Fig 4: Login with AD user

Step 3: And now you can run the query to add Active directory user and give permission as you want, though traditional database level roles like db_datareader, db_datawriter, db_ddladmin, etc. are the same in Azure database, but roles like sysadmin, serveradmin, etc. don’t exist in Azure SQL database. There are two admin roles, dbmanager (similar to dbcreator) that can create and drop databases, and loginmanager (similar to securityadmin) that can create new logins which you can see in the below code:

CREATE USER [user1@domain.com] 

FROM EXTERNAL PROVIDER 

WITH DEFAULT_SCHEMA = dbo;  

  

-- add user to role(s) for the particular database

ALTER ROLE dbmanager ADD MEMBER [user1@domain.com]; 

ALTER ROLE loginmanager ADD MEMBER [user1@domain.com]; 

As soon as you execute the above code the AD user got all the necessary access to perform activities. 

  

Finding unused Schemas in SQL database

Fun part of a developer life is: 'Today what is unknown, tomorrow you know it by learning and doing". And by learning the unknown and implementing you become expert :) I will share something which I learned and develop recently. Long back I got a task to find out used schemas in database where the solution was pretty straight forward. However, this time the task was to get unused schemas. Firstly, I thought this must be fun, never did it but someone must did it so started with google and got some clue but not with proper explanation so thought about writing it.

To find out the solution, you need to go through three tables:
1) Sys.Schemas
2) Sys.database_principals
3) Sys.objects

Easy way to find unused schemas is : If the schema_id from sys.schemas table is not found in Sys.objects table means those are never used. And both tables schemas and objects got the column ‘Schema_ID’. Woww!! We know the solution, yes all most 😊 Why we need database_principals table? When schema name is not specified for those cases we need to use this table. Relations among three tables can be depicted with below diagram:

Fig 1: Relationship among tables

We covered theory part, let's see in action, below is the SQL code to find out unused schemas in database:

SELECT name

FROM sys.schemas SCH

where SCH.schema_id not between 1 and 4    --This is for dbo, guest, information_schema,sys

and SCH.principal_id not between 16384 and 16399 ---  it's related to db_* schema, so don't touch this.

and not exists

( select 1

from sys.database_principals P

where P.default_schema_name=SCH.name     ---Name to be used when SQL name does not specify a schema, so we are taking this off

)

and not exists

( select 1

from sys.objects O

where O.[schema_id]=SCH.[schema_id]   ---Object uses the schema, so if schema id is not exist in the object; it means the schema is not used.

)

order by SCH.name

You know how to find unused schema, now you can delete them if you want.

What you need to know about DBC in Teradata?

As a database developer you may think DBC* is for database administrator to care about, not for you as a database developer.

However, knowing about DBC can save your time when it come to query like. e.g. you need to  know all the tables in your database? or Column names in a table or existing views in a database. Sometimes, you may need to anonymous some of the columns in a few tables that you need to do using DBC.

You could say, I can see all table names by browsing the database table and can copy and paste names of all the tables. Yes, you can do it but that is time consumable and manual process. Would not it be nice if you can get all the table names by writing two lines of code.

Lets say, you want to find out all the tables name in your database then SQL will look like below:

Select * from dbc.tables where tablekind='T' and databasename='STUDENT103'

which will return below result (part of the full result):

Fig 1: Tables list of the database

To find out the view names from the database then SQL will look like below:

Select * from dbc.tables where tablekind='V' and databasename='STUDENT103'

Part of the result set look like below:

Fig 2: View list of the database(database contains only one view)

To get all the column names of a table of database, you need to write below SQL:

SELECT DatabaseName,TableName,ColumnName
FROM DBC.Columns
WHERE DatabaseName='STUDENT103'
AND TableName='DEPARTMENT';

Result of the above code will look like below:

Fig 3: List of columns in a table

By using DBC you can get not only column, table, view names but also more attributes like who created the table or view, when it's created and so on.

Note: DBC is the superuser on Teradata database