Server Roles and Azure Active Directory in an Azure SQL Database

This video explains the built in server roles in an Azure SQL Server and demonstrates how to give a login a server roles and how to give an Azure Active Directory user permissions to an Azure SQL database.

[@James-sc1lz]

Subscribed. Awesome video. I realise video was created back in 2017. Now, I guess you could just populate AD Admin in the portal for SQL PaaS to achieve the same thing for AD Account admin to create logins etc.

[@mohammedmoinudheen773]

Awesome video, I downloaded the ebook. Clear explanation :-)

[@bn_ln]

thank you sir!

[@mason60638]

This was very helpful.
Thank you

[@ananthkrishnabattepati]

I have added a security group for Active Integrated Authentication, but my users are facing below error when they try to connect Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

[@DavidPostlethwaiteSQL]

That sounds like a Kerberos error. How are you connecting to the database?

[@ananthkrishnabattepati]

Thank you David for the reply, it's working fine now, this video helped me a lot to resolve one big issue that I was facing from the past few weeks, one more clarification I do require is, isn't possible to access the Azure SQL DB without adding our IP address in the Azure portal or giving range of IP address so that users in that range can access the database

[@heathweathe]

Can you provide the script for the login permissions that you were using at 3:40 ?

[@DavidPostlethwaiteSQL]

DROP User if exists [User1]
DROP Login [User1]
GO
CREATE LOGIN [User1] WITH PASSWORD=N'XXXXXX'
GO
CREATE USER [User1] FROM LOGIN User1;
GO
select * from [sys].[sql_logins]
EXEC sp_addrolemember 'dbmanager', 'User1';
EXEC sp_addrolemember 'loginmanager', 'User1';
--Change Database using the GUI Dropdown
CREATE USER USER1
FOR LOGIN User1
WITH DEFAULT_SCHEMA = dbo
GO
-- Add user to a database role
EXEC sp_addrolemember N'db_owner', N'USER1'
EXEC sp_addrolemember N'db_datawriter', N'USER1'
EXEC sp_addrolemember N'db_datareader', N'USER1'
EXEC sp_addrolemember N'db_ddladmin', N'USER1'
GO
select m.name as Member, r.name as Role
from sys.database_role_members
inner join sys.database_principals m on sys.database_role_members.member_principal_id = m.principal_id
inner join sys.database_principals r on sys.database_role_members.role_principal_id = r.principal_id

[@parasharamit]

Thanks. It helped.

[@anujsoni8056]

Hi David.
It is really nice explanation..
I have my database in azure cloud and azure active directory is already set up. Now I would like to access users information (like we do in LDAP windows active directory using SQL query) in azure cloud database from azure active directory..
Can you please explain that query part or way how we can do this.
Thanks in advance.