Last active
July 28, 2025 18:12
-
-
Save taniomi/64716da6cc87243e4957be67d87b3571 to your computer and use it in GitHub Desktop.
Create a Microsoft Entra ID role in SQL Server for logging with MFA in SQL Server
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| DECLARE @role SYSNAME = 'role'; | |
| -- 1. Create database role | |
| EXEC('CREATE ROLE [' + @role + ']'); | |
| -- 2. Create user from Microsoft Entra ID group and add to the role | |
| EXEC('CREATE USER [dw-group-' + @role + '] FROM EXTERNAL PROVIDER;'); | |
| EXEC('ALTER ROLE [' + @role + '] ADD MEMBER [dw-group-' + @role + '];'); | |
| -- 3. Grant permissions dynamically | |
| --DECLARE @role SYSNAME = 'nvt_insert'; | |
| DECLARE @sql NVARCHAR(MAX) = ''; | |
| SELECT @sql += | |
| 'GRANT SELECT ON SCHEMA::' + QUOTENAME(name) + ' TO [' + @role + '];' + CHAR(13) + | |
| 'GRANT INSERT ON SCHEMA::' + QUOTENAME(name) + ' TO [' + @role + '];' + CHAR(13) + | |
| 'GRANT EXECUTE ON SCHEMA::' + QUOTENAME(name) + ' TO [' + @role + '];' + CHAR(13) + | |
| 'GRANT VIEW DEFINITION ON SCHEMA::' + QUOTENAME(name) + ' TO [' + @role + '];' + CHAR(13) | |
| FROM sys.schemas | |
| WHERE name NOT IN ('dbo', 'dev', 'INFORMATION_SCHEMA') | |
| AND name NOT LIKE 'db_%' | |
| AND name NOT LIKE 'sys%'; | |
| -- Check and run | |
| PRINT @sql; | |
| EXEC sp_executesql @sql; | |
| -- See the granted permissions | |
| EXEC(' | |
| SELECT | |
| dp.name AS principal_name, | |
| dp.type_desc AS principal_type, | |
| perm.class_desc, | |
| perm.permission_name, | |
| perm.state_desc, | |
| obj.name AS object_name | |
| FROM sys.database_permissions perm | |
| LEFT JOIN sys.objects obj ON perm.major_id = obj.object_id | |
| JOIN sys.database_principals dp ON perm.grantee_principal_id = dp.principal_id | |
| WHERE dp.name = [' + @role + ']; | |
| '); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment