Friday, April 26, 2024

Granting Read-Only Access to an AD Group to an Azure SQL Database

This morning I got a request to grant read-only database access to an AD Group.  Here's how I did it:

Using SSMS, I ran these commands both in the master and the user database specified on the request.  The reason why I added the user to the master database without any permissions is to allow login to succeed without defining a default database on the connection string.

Here's the code:

In the master database:

CREATE USER [AD Group Name] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]
GO


In the User database:

CREATE USER [AD Group Name] FROM  EXTERNAL PROVIDER  WITH DEFAULT_SCHEMA=[dbo]
GO

EXEC sp_addrolemember 'db_datareader', [AD Group Name]
GO

No comments:

Post a Comment

Little script to see the backup history

 Today, I was teaching a co-worker how to create a copy only backup of a SQL Managed Instance database.  And I came up to use this script t...