Skip to content

Instantly share code, notes, and snippets.

@JosiahSiegel

JosiahSiegel/usp_ConfigureSecurity.sql

Created March 26, 2026 22:11
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save JosiahSiegel/27aa135fce8af547cf76615ad53ad158 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save JosiahSiegel/27aa135fce8af547cf76615ad53ad158 to your computer and use it in GitHub Desktop.
Download ZIP
SQL Instance Permissions Management
Raw
usp_ConfigureSecurity.sql
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ============================================================================
-- Table type for the security matrix TVP
-- Must drop SP first (it references the type), then drop/recreate the type.
-- ============================================================================
IF EXISTS (SELECT 1 FROM sys.procedures WHERE name = 'usp_ConfigureSecurity' AND schema_id = SCHEMA_ID('dbo'))
DROP PROCEDURE dbo.usp_ConfigureSecurity;
GO
IF EXISTS (SELECT 1 FROM sys.types WHERE name = 'SecurityMatrixType' AND is_table_type = 1)
DROP TYPE dbo.SecurityMatrixType;
GO
CREATE TYPE dbo.SecurityMatrixType AS TABLE (
PrincipalName SYSNAME NOT NULL,
PrincipalType VARCHAR(10) NOT NULL, -- 'SQL' or 'WINDOWS' (or 'WIN' shorthand)
ServerPattern VARCHAR(50) NOT NULL,
DatabaseName SYSNAME NOT NULL,
DefaultSchema SYSNAME NOT NULL DEFAULT 'dbo',
Flags VARCHAR(50) NOT NULL DEFAULT '', -- dash-delimited: R W A O Ex VD SP
ServerRole VARCHAR(50) NULL,
ServerGrant VARCHAR(100) NULL,
CustomRole SYSNAME NULL,
ExtraGrants NVARCHAR(MAX) NULL
);
GO
CREATE PROCEDURE [dbo].[usp_ConfigureSecurity]
@Matrix SecurityMatrixType READONLY,
@DryRun BIT = 0,
@TargetDatabase SYSNAME = NULL,
@Verbose INT = 0,
@EnforceMatrix BIT = 0
/*******************************************************************************
EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix; -- apply all (additive only)
EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @EnforceMatrix = 1; -- apply all + revoke extras + drop extra users
EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @DryRun = 1; -- preview changes only
EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @DryRun = 1, @Verbose = 1; -- preview all (incl. OK)
EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @TargetDatabase = 'MyAppDB'; -- single DB
EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @DryRun = 1, @TargetDatabase = 'MyAppDB'; -- preview single DB
Idempotent stored procedure to configure database security for SQL Server systems.
The security matrix is passed in as a Table-Valued Parameter (@Matrix) of type
dbo.SecurityMatrixType. Each row is one principal + one database + a flags string
encoding its permissions for that combination.
PARAMETERS:
@Matrix SecurityMatrixType READONLY
Table-valued parameter containing the security matrix.
Must have at least one row or the SP will raise an error.
Columns:
PrincipalName SYSNAME -- login / group name
PrincipalType VARCHAR(10) -- 'SQL', 'WINDOWS', or 'WIN' (shorthand)
ServerPattern VARCHAR(50) -- LIKE pattern matched against @@SERVERNAME
DatabaseName SYSNAME -- exact DB name, '*' for all user DBs,
-- or '*,-DB1,-DB2' for all EXCEPT listed DBs
DefaultSchema SYSNAME -- default schema (usually 'dbo')
Flags VARCHAR(50) -- dash-delimited permission flags (see key below)
ServerRole VARCHAR(50) -- e.g. 'bulkadmin'
ServerGrant VARCHAR(100) -- e.g. 'VIEW SERVER STATE'
CustomRole SYSNAME -- creates this DB role, adds principal as member
ExtraGrants NVARCHAR(MAX) -- pipe-delimited GRANT statements (fully written, executed as-is)
@DryRun BIT = 0 When 0 (default): applies all security changes normally.
When 1: performs a read-only comparison of the matrix's
desired state against the current server state and outputs
a report showing what WOULD change, without making any
modifications. Lines are prefixed with:
[OK] - permission already matches desired state
[ADD] - permission needs to be added
[DROP] - user/role exists on server but not in matrix
[REVOKE] - permission exists on server but not in matrix
[MISS] - server-level login does not exist
@Verbose INT = 0 Controls output detail level.
0 (default): In dry-run mode, shows only pending changes
(ADD, DROP, REVOKE, MISS) and hides OK confirmations.
In normal mode, shows all actions taken.
Summary and warnings always shown.
1: Shows everything including dry-run OK confirmations.
@TargetDatabase SYSNAME = NULL
When NULL (default): processes all user databases.
When set to a database name: processes only that single
database. Server-level permissions (Phase 3) are always
processed regardless of this setting.
@EnforceMatrix BIT = 0
Controls whether extra permissions are removed.
Applies to BOTH dry-run and normal execution modes.
0 (default, safe): Only ADD missing permissions. Never
revoke or drop anything. Safe for production use.
- Creates missing logins, users, role memberships, grants
- Never removes extra roles, grants, or users
- In dry-run mode, hides [DROP] and [REVOKE] items
1 (strict): Also REVOKE extra role memberships, REVOKE
extra grants, and DROP users not in the matrix.
- Same additive behavior as 0, plus:
- Revokes standard role memberships not in the matrix
- Revokes standard grants (EXECUTE, VIEW DEFINITION,
SHOWPLAN) not in the matrix
- Drops database users that are not in the matrix
- In dry-run mode, shows [DROP] and [REVOKE] items
FLAGS KEY (dash-delimited in the Flags column):
R = db_datareader (adds principal to db_datareader role)
W = db_datawriter (adds principal to db_datawriter role)
A = db_ddladmin (adds principal to db_ddladmin role)
O = db_owner (adds principal to db_owner role)
Ex = GrantExecute (GRANT EXECUTE TO principal)
VD = GrantViewDef (GRANT VIEW DEFINITION TO principal)
SP = GrantShowplan (GRANT SHOWPLAN TO principal)
Examples: 'R-W' = reader+writer | 'O' = owner only | 'R-W-A-Ex' = all four
PrincipalType shorthand:
'WIN' is automatically expanded to 'WINDOWS' inside the SP.
ServerPattern uses LIKE syntax to match @@SERVERNAME:
'SERVER-01' = one specific server
'SQL-DEV%' = all servers starting with SQL-DEV
'SQL-[DQ]%' = servers starting with SQL-D or SQL-Q (e.g., DEV and QA)
'%' = all servers
DatabaseName:
Exact name = that specific database only
'*' = ALL user databases (system/excluded DBs are skipped automatically)
'*,-DB1,-DB2' = all user databases EXCEPT DB1 and DB2 (comma-separated, dash-prefixed)
ServerRole: e.g. 'bulkadmin' -- adds principal to that server role
ServerGrant: e.g. 'VIEW SERVER STATE' -- GRANT <perm> TO principal at server level
CustomRole: e.g. 'AppDataRole' -- creates this DB role, adds the principal
as a member, then grants the role the flagged standard roles
ExtraGrants: pipe-delimited custom GRANT statements executed as-is in the database
context. Write the full statement including the principal name, e.g.:
'GRANT CONTROL ON CERTIFICATE::cert_sk_apps TO [my-api]'
-- =============================================================================
-- EXAMPLE USAGE:
-- =============================================================================
--
-- DECLARE @SecurityMatrix SecurityMatrixType;
-- INSERT INTO @SecurityMatrix
-- (PrincipalName, PrincipalType, ServerPattern, DatabaseName, DefaultSchema,
-- Flags, ServerRole, ServerGrant, CustomRole, ExtraGrants)
-- VALUES
-- -- A SQL login with read/write on a specific DB, plus server-level bulkadmin
-- ('app-service-account', 'SQL', 'SQL-[DQ]%', 'MyAppDB', 'dbo', 'R-W', 'bulkadmin', NULL, NULL, NULL),
-- -- A Windows group with db_owner on all user databases + VIEW SERVER STATE
-- ('DOMAIN\DBA Team', 'WIN', '%', '*', 'dbo', 'O-VD-SP', NULL, 'VIEW SERVER STATE', NULL, NULL),
-- -- A Windows group with read on all user databases EXCEPT P350 and MRA
-- ('DOMAIN\Readers', 'WIN', '%', '*,-P350,-MRA','dbo', 'R', NULL, NULL, NULL, NULL),
-- -- A SQL login via custom role pattern
-- ('etl-service', 'SQL', 'SQL-[DQ]%', 'StagingDB', 'dbo', 'O', NULL, NULL, 'ETLRole', NULL),
-- -- A login with extra grants (pipe-delimited)
-- ('report-reader', 'SQL', '%', 'ReportsDB', 'dbo', 'R', NULL, NULL, NULL,
-- 'GRANT VIEW DEFINITION ON SYMMETRIC KEY::sk_Reports TO [report-reader]');
--
-- EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix; -- apply all
-- EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @DryRun = 1; -- preview changes only
-- EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @DryRun = 1, @Verbose = 1; -- preview all (incl. OK)
-- EXEC dbo.usp_ConfigureSecurity @Matrix = @SecurityMatrix, @TargetDatabase = 'MyAppDB'; -- single DB
HISTORY:
See source control for version history.
*******************************************************************************/
AS
BEGIN TRY
SET NOCOUNT ON;
DECLARE @MESSAGE VARCHAR(8000);
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @DynSQL NVARCHAR(MAX);
DECLARE @CurrentDB SYSNAME;
-- ========================================================================
-- P R O C E S S I N G L O G I C
-- ========================================================================
DECLARE @ExcludedDBs TABLE (DbName SYSNAME NOT NULL);
INSERT INTO @ExcludedDBs (DbName) VALUES ('master'),('model'),('msdb'),('tempdb'),('ssisdb');
-- Add any additional databases to exclude from processing:
-- INSERT INTO @ExcludedDBs (DbName) VALUES ('AdminDB'),('MonitoringDB');
-- Validate @TargetDatabase if specified
IF @TargetDatabase IS NOT NULL
BEGIN
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabase)
BEGIN
RAISERROR('Error - Database [%s] does not exist on this server.', 16, 1, @TargetDatabase) WITH NOWAIT;
RETURN;
END
IF @TargetDatabase IN (SELECT DbName FROM @ExcludedDBs)
BEGIN
RAISERROR('Error - Database [%s] is in the excluded list and cannot be targeted.', 16, 1, @TargetDatabase) WITH NOWAIT;
RETURN;
END
SET @MESSAGE = 'Targeting single database: [' + @TargetDatabase + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
-- Validate that @Matrix is not empty
IF NOT EXISTS (SELECT 1 FROM @Matrix)
BEGIN
RAISERROR('Error - @Matrix is empty. No security configuration to process.', 16, 1) WITH NOWAIT;
RETURN;
END
-- ========================================================================
-- Parse the flags-string TVP into an internal table with individual BIT columns
-- ========================================================================
DECLARE @Parsed TABLE (
PrincipalName SYSNAME NOT NULL,
PrincipalType VARCHAR(10) NOT NULL,
ServerPattern VARCHAR(50) NOT NULL,
DatabaseName SYSNAME NOT NULL,
DefaultSchema SYSNAME NOT NULL,
db_datareader BIT NOT NULL,
db_datawriter BIT NOT NULL,
db_ddladmin BIT NOT NULL,
db_owner BIT NOT NULL,
GrantExecute BIT NOT NULL,
GrantViewDef BIT NOT NULL,
GrantShowplan BIT NOT NULL,
ServerRole VARCHAR(50) NULL,
ServerGrant VARCHAR(100) NULL,
CustomRole SYSNAME NULL,
ExtraGrants NVARCHAR(MAX) NULL
);
INSERT INTO @Parsed
SELECT
PrincipalName,
CASE WHEN PrincipalType = 'WIN' THEN 'WINDOWS' ELSE PrincipalType END,
ServerPattern, DatabaseName, DefaultSchema,
IIF(Flags LIKE '%R%' AND Flags NOT LIKE '%Re%', 1, 0), -- R = db_datareader
IIF(Flags LIKE '%W%', 1, 0), -- W = db_datawriter
IIF(Flags LIKE '%A%', 1, 0), -- A = db_ddladmin
IIF(Flags LIKE '%O%', 1, 0), -- O = db_owner
IIF(Flags LIKE '%Ex%', 1, 0), -- Ex = GrantExecute
IIF(Flags LIKE '%VD%', 1, 0), -- VD = GrantViewDef
IIF(Flags LIKE '%SP%', 1, 0), -- SP = GrantShowplan
ServerRole, ServerGrant, CustomRole, ExtraGrants
FROM @Matrix;
DECLARE @Actions TABLE (ActionType VARCHAR(10) NOT NULL, ActionTarget NVARCHAR(500) NOT NULL);
DECLARE @EnsuredLogins TABLE (LoginName SYSNAME NOT NULL);
DECLARE @db_name SYSNAME, @prinName SYSNAME, @prinType VARCHAR(10), @defSchema SYSNAME;
DECLARE @fReader BIT, @fWriter BIT, @fDDLAdmin BIT, @fOwner BIT;
DECLARE @fExec BIT, @fViewDef BIT, @fShowplan BIT;
DECLARE @actType VARCHAR(10), @actTarget NVARCHAR(500);
DECLARE @extraGrants NVARCHAR(MAX), @grantStmt NVARCHAR(MAX), @delimPos INT;
-- Dry-run counters and state variables (declared at top level for T-SQL scoping)
DECLARE @srv_OK INT, @srv_ADD INT, @srv_MISS INT;
DECLARE @db_OK INT, @db_ADD INT, @db_DROP INT, @db_MISS INT;
DECLARE @exists BIT;
DECLARE @srv_Principal SYSNAME, @srv_PrinType VARCHAR(10), @srv_PermType VARCHAR(10), @srv_PermValue VARCHAR(100);
DECLARE @cr_RoleName SYSNAME, @cr_Member SYSNAME, @cr_PrinType VARCHAR(10), @cr_Schema SYSNAME;
DECLARE @roleList VARCHAR(500);
DECLARE @grantList VARCHAR(500);
DECLARE @eg_check_nouser NVARCHAR(MAX);
DECLARE @currentSchema SYSNAME;
DECLARE @checkRoles TABLE (RoleName SYSNAME NOT NULL);
DECLARE @checkRole SYSNAME;
DECLARE @checkGrants TABLE (GrantName SYSNAME NOT NULL);
DECLARE @checkGrant SYSNAME;
DECLARE @eg_check NVARCHAR(MAX);
DECLARE @eg_count INT;
DECLARE @memberExists BIT;
DECLARE @inCustomRole BIT;
DECLARE @crCheckRoles TABLE (RoleName SYSNAME NOT NULL);
DECLARE @crCheckRole SYSNAME;
DECLARE @crRoleExists BIT;
DECLARE @ExpectedPrincipals TABLE (PName SYSNAME NOT NULL);
DECLARE @ExtraUsers TABLE (UserName SYSNAME NOT NULL);
DECLARE @extraUser SYSNAME;
DECLARE @totalChanges INT;
DECLARE @userRoles TABLE (RoleName SYSNAME NOT NULL);
DECLARE @userGrants TABLE (GrantName SYSNAME NOT NULL);
DECLARE @extraRole SYSNAME;
DECLARE @extraGrant SYSNAME;
DECLARE @db_REVOKE INT;
-- ========================================================================
-- UNIFIED CODE PATH: single pass for both dry-run and normal execution
-- Dry-run (@DryRun=1): checks state and reports [OK]/[ADD]/[MISS]/[DROP]/[REVOKE]
-- Normal (@DryRun=0): checks state, reports, and acts (idempotent)
-- ========================================================================
-- Initialize dry-run counters (always -- cheap, and avoids NULL in summary)
SET @srv_OK = 0; SET @srv_ADD = 0; SET @srv_MISS = 0;
SET @db_OK = 0; SET @db_ADD = 0; SET @db_DROP = 0; SET @db_MISS = 0; SET @db_REVOKE = 0;
-- Mode-specific banners
IF @DryRun = 1
BEGIN
RAISERROR('=== DRY RUN MODE: No changes will be made ===', 0, 42) WITH NOWAIT;
RAISERROR('=== Comparing matrix desired state vs. current server state ===', 0, 42) WITH NOWAIT;
IF @EnforceMatrix = 1
RAISERROR('*** @EnforceMatrix = 1: will also show extras to REVOKE/DROP ***', 0, 42) WITH NOWAIT;
END
ELSE
BEGIN
IF @EnforceMatrix = 1
RAISERROR('*** @EnforceMatrix = 1: extras will be revoked/dropped ***', 0, 42) WITH NOWAIT;
END
IF @Verbose = 0
RAISERROR('(Use @Verbose = 1 for full detail)', 0, 42) WITH NOWAIT;
RAISERROR('', 0, 42) WITH NOWAIT;
-- ==============================================================
-- PHASE 3: Server-level permissions (unified)
-- ==============================================================
IF @DryRun = 1
RAISERROR('=== Server-Level Permissions ===', 0, 42) WITH NOWAIT;
ELSE
RAISERROR('=== PHASE 3: Applying server-level permissions ===', 0, 42) WITH NOWAIT;
DECLARE cur_SrvPerms CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT PrincipalName, PrincipalType, 'ROLE', ServerRole
FROM @Parsed WHERE ServerRole IS NOT NULL AND ServerRole <> '' AND @@SERVERNAME LIKE ServerPattern
UNION
SELECT DISTINCT PrincipalName, PrincipalType, 'GRANT', ServerGrant
FROM @Parsed WHERE ServerGrant IS NOT NULL AND ServerGrant <> '' AND @@SERVERNAME LIKE ServerPattern;
OPEN cur_SrvPerms; FETCH NEXT FROM cur_SrvPerms INTO @srv_Principal, @srv_PrinType, @srv_PermType, @srv_PermValue;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Ensure Windows login exists (normal mode only, deduplicated)
IF @DryRun = 0 AND @srv_PrinType = 'WINDOWS' AND NOT EXISTS (SELECT 1 FROM @EnsuredLogins WHERE LoginName = @srv_Principal)
BEGIN
INSERT INTO @EnsuredLogins (LoginName) VALUES (@srv_Principal);
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @srv_Principal)
BEGIN
SET @DynSQL = N'CREATE LOGIN ' + QUOTENAME(@srv_Principal) + N' FROM WINDOWS WITH DEFAULT_DATABASE=[master]';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = 'Created login [' + @srv_Principal + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = 'WARNING: Could not create login [' + @srv_Principal + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
-- Check if login exists at server level
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @srv_Principal)
BEGIN
-- Dry-run: report MISS; Normal mode: the CREATE LOGIN above already failed or login is SQL type
IF @DryRun = 1
BEGIN
IF @srv_PermType = 'ROLE'
SET @MESSAGE = '[MISS] Server role [' + @srv_PermValue + '] needs member [' + @srv_Principal + '] but login does not exist';
ELSE
SET @MESSAGE = '[MISS] Server grant [' + @srv_PermValue + '] needs [' + @srv_Principal + '] but login does not exist';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @srv_MISS = @srv_MISS + 1;
END
END
ELSE IF @srv_PermType = 'ROLE'
BEGIN
IF EXISTS (
SELECT 1 FROM sys.server_role_members m
JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
JOIN sys.server_principals r ON r.principal_id = m.role_principal_id
WHERE p.name = @srv_Principal AND r.name = @srv_PermValue)
BEGIN
SET @srv_OK = @srv_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] Server role [' + @srv_PermValue + '] already has member [' + @srv_Principal + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] Server role [' + @srv_PermValue + '] needs member [' + @srv_Principal + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @srv_ADD = @srv_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'ALTER SERVER ROLE ' + QUOTENAME(@srv_PermValue) + N' ADD MEMBER ' + QUOTENAME(@srv_Principal);
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = 'Added [' + @srv_Principal + '] to server role [' + @srv_PermValue + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = 'WARNING: Could not add [' + @srv_Principal + '] to role [' + @srv_PermValue + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
END
ELSE -- GRANT
BEGIN
IF EXISTS (
SELECT 1 FROM sys.server_permissions sp
JOIN sys.server_principals pr ON sp.grantee_principal_id = pr.principal_id
WHERE sp.permission_name = @srv_PermValue AND pr.name = @srv_Principal)
BEGIN
SET @srv_OK = @srv_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] Server grant [' + @srv_PermValue + '] already granted to [' + @srv_Principal + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] Server grant [' + @srv_PermValue + '] needs to be granted to [' + @srv_Principal + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @srv_ADD = @srv_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'GRANT ' + @srv_PermValue + N' TO ' + QUOTENAME(@srv_Principal);
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = 'Granted [' + @srv_PermValue + '] to [' + @srv_Principal + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = 'WARNING: Could not grant [' + @srv_PermValue + '] to [' + @srv_Principal + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
END
FETCH NEXT FROM cur_SrvPerms INTO @srv_Principal, @srv_PrinType, @srv_PermType, @srv_PermValue;
END
CLOSE cur_SrvPerms; DEALLOCATE cur_SrvPerms;
-- ==============================================================
-- PHASE 4: Database-level security (unified)
-- ==============================================================
RAISERROR('', 0, 42) WITH NOWAIT;
IF @DryRun = 1
RAISERROR('=== Database-Level Permissions ===', 0, 42) WITH NOWAIT;
ELSE
RAISERROR('=== PHASE 4: Applying database-level security ===', 0, 42) WITH NOWAIT;
DECLARE cur_Databases CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE name NOT IN (SELECT DbName FROM @ExcludedDBs)
AND name NOT LIKE 'ReportServer%' AND name NOT LIKE '%SNAPSHOT%'
AND state_desc = 'ONLINE'
AND (@TargetDatabase IS NULL OR name = @TargetDatabase)
ORDER BY name;
OPEN cur_Databases; FETCH NEXT FROM cur_Databases INTO @db_name;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DryRun = 1
BEGIN SET @MESSAGE = '--- Database: [' + @db_name + '] ---'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END
ELSE
BEGIN SET @MESSAGE = '--- Processing database: [' + @db_name + '] ---'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END
-- ---- Phase 4a: Standard principals (non-CustomRole rows) ----
DECLARE cur_Principals CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT PrincipalName, PrincipalType FROM @Parsed
WHERE @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0))
AND CustomRole IS NULL ORDER BY PrincipalName;
OPEN cur_Principals; FETCH NEXT FROM cur_Principals INTO @prinName, @prinType;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Resolve schema and aggregate flags (single query set for both modes)
SET @defSchema = 'dbo';
SELECT TOP 1 @defSchema = DefaultSchema FROM @Parsed
WHERE PrincipalName = @prinName AND @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0))
AND CustomRole IS NULL AND DefaultSchema <> 'dbo';
SELECT @fReader = MAX(CAST(db_datareader AS INT)), @fWriter = MAX(CAST(db_datawriter AS INT)),
@fDDLAdmin = MAX(CAST(db_ddladmin AS INT)), @fOwner = MAX(CAST(db_owner AS INT)),
@fExec = MAX(CAST(GrantExecute AS INT)), @fViewDef = MAX(CAST(GrantViewDef AS INT)),
@fShowplan = MAX(CAST(GrantShowplan AS INT))
FROM @Parsed WHERE PrincipalName = @prinName AND @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0))
AND CustomRole IS NULL;
-- Check if login exists at server level
IF @prinType = 'SQL' AND NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @prinName)
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[MISS] User [' + @prinName + '] -- login does not exist on server';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_MISS = @db_MISS + 1;
END
FETCH NEXT FROM cur_Principals INTO @prinName, @prinType;
CONTINUE;
END
-- Ensure Windows login exists (normal mode only, deduplicated)
IF @prinType = 'WINDOWS' AND NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @prinName)
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] Windows login [' + @prinName + '] will be created on server';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
-- Don't CONTINUE here -- still check database-level state
END
ELSE IF NOT EXISTS (SELECT 1 FROM @EnsuredLogins WHERE LoginName = @prinName)
BEGIN
INSERT INTO @EnsuredLogins (LoginName) VALUES (@prinName);
SET @DynSQL = N'CREATE LOGIN ' + QUOTENAME(@prinName) + N' FROM WINDOWS WITH DEFAULT_DATABASE=[master]';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = 'Created login [' + @prinName + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = 'WARNING: Could not create login [' + @prinName + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
ELSE IF @DryRun = 0 AND @prinType = 'WINDOWS' AND NOT EXISTS (SELECT 1 FROM @EnsuredLogins WHERE LoginName = @prinName)
BEGIN
-- Login exists but not yet in dedup table -- just register it
INSERT INTO @EnsuredLogins (LoginName) VALUES (@prinName);
END
-- Check if database user exists (dynamic SQL to query target database)
SET @exists = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_principals WHERE name = @userName AND type NOT IN (''R'')
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@userName SYSNAME, @exists BIT OUTPUT',
@userName = @prinName, @exists = @exists OUTPUT;
END TRY
BEGIN CATCH
IF @DryRun = 1
SET @MESSAGE = '[WARN] Could not query [' + @db_name + '] for user [' + @prinName + ']: ' + ERROR_MESSAGE();
ELSE
SET @MESSAGE = 'WARNING: Could not query [' + @db_name + '] for user [' + @prinName + ']: ' + ERROR_MESSAGE();
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
FETCH NEXT FROM cur_Principals INTO @prinName, @prinType;
CONTINUE;
END CATCH
IF @exists = 0
BEGIN
IF @DryRun = 1
BEGIN
-- User does not exist: report everything that needs to be added
SET @roleList = '';
IF @fReader = 1 SET @roleList = @roleList + 'db_datareader, ';
IF @fWriter = 1 SET @roleList = @roleList + 'db_datawriter, ';
IF @fDDLAdmin = 1 SET @roleList = @roleList + 'db_ddladmin, ';
IF @fOwner = 1 SET @roleList = @roleList + 'db_owner, ';
IF LEN(@roleList) > 0 SET @roleList = LEFT(@roleList, LEN(@roleList) - 1);
ELSE SET @roleList = '(none)';
SET @grantList = '';
IF @fExec = 1 SET @grantList = @grantList + 'EXECUTE, ';
IF @fViewDef = 1 SET @grantList = @grantList + 'VIEW DEFINITION, ';
IF @fShowplan = 1 SET @grantList = @grantList + 'SHOWPLAN, ';
IF LEN(@grantList) > 0 SET @grantList = LEFT(@grantList, LEN(@grantList) - 1);
SET @MESSAGE = '[ADD] User [' + @prinName + '] needs to be created (schema=' + @defSchema
+ ', roles: ' + @roleList
+ CASE WHEN LEN(@grantList) > 0 THEN ', grants: ' + @grantList ELSE '' END + ')';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
-- Report ExtraGrants that would also be applied
DECLARE cur_EGCheck CURSOR LOCAL FAST_FORWARD FOR
SELECT ExtraGrants FROM @Parsed WHERE PrincipalName = @prinName AND @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0))
AND CustomRole IS NULL AND ExtraGrants IS NOT NULL;
OPEN cur_EGCheck; FETCH NEXT FROM cur_EGCheck INTO @extraGrants;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @extraGrants = @extraGrants + N'|';
WHILE LEN(@extraGrants) > 1
BEGIN
SET @delimPos = CHARINDEX(N'|', @extraGrants);
SET @grantStmt = LTRIM(RTRIM(LEFT(@extraGrants, @delimPos - 1)));
SET @extraGrants = SUBSTRING(@extraGrants, @delimPos + 1, LEN(@extraGrants));
IF LEN(@grantStmt) > 0
BEGIN
SET @MESSAGE = '[ADD] User [' + @prinName + '] will also need ExtraGrant: ' + @grantStmt;
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
END
FETCH NEXT FROM cur_EGCheck INTO @extraGrants;
END
CLOSE cur_EGCheck; DEALLOCATE cur_EGCheck;
END
ELSE
BEGIN
-- Normal mode: create the user
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; CREATE USER ' + QUOTENAME(@prinName) + N' WITH DEFAULT_SCHEMA=' + QUOTENAME(@defSchema) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = 'Created user [' + @prinName + '] in [' + @db_name + '] (schema=' + @defSchema + ')'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH
SET @MESSAGE = 'WARNING: Could not create user [' + @prinName + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
FETCH NEXT FROM cur_Principals INTO @prinName, @prinType; CONTINUE;
END CATCH
END
END
ELSE IF @DryRun = 1
BEGIN
-- User exists in dry-run: check schema
SET @currentSchema = '';
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @sch = dp.default_schema_name FROM sys.database_principals dp WHERE dp.name = @userName';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@userName SYSNAME, @sch SYSNAME OUTPUT',
@userName = @prinName, @sch = @currentSchema OUTPUT;
END TRY
BEGIN CATCH SET @currentSchema = '(unknown)'; END CATCH
IF ISNULL(@currentSchema, 'dbo') = @defSchema
BEGIN
SET @db_OK = @db_OK + 1;
IF @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] User [' + @prinName + '] exists with correct schema [' + @defSchema + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
SET @MESSAGE = '[ADD] User [' + @prinName + '] exists but schema is [' + ISNULL(@currentSchema,'NULL') + '], expected [' + @defSchema + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
END
-- (Normal mode with user existing: no action needed for user creation, proceed to roles/grants)
-- Skip role/grant processing for dry-run when user does not exist (already reported above)
IF @DryRun = 1 AND @exists = 0
BEGIN
FETCH NEXT FROM cur_Principals INTO @prinName, @prinType;
CONTINUE;
END
-- ---- Role membership checks (unified) ----
DELETE FROM @checkRoles;
IF @fReader = 1 INSERT INTO @checkRoles VALUES ('db_datareader');
IF @fWriter = 1 INSERT INTO @checkRoles VALUES ('db_datawriter');
IF @fDDLAdmin = 1 INSERT INTO @checkRoles VALUES ('db_ddladmin');
IF @fOwner = 1 INSERT INTO @checkRoles VALUES ('db_owner');
DECLARE cur_Roles CURSOR LOCAL FAST_FORWARD FOR SELECT RoleName FROM @checkRoles;
OPEN cur_Roles; FETCH NEXT FROM cur_Roles INTO @checkRole;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exists = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name = @roleName AND m.name = @userName
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@roleName SYSNAME, @userName SYSNAME, @exists BIT OUTPUT',
@roleName = @checkRole, @userName = @prinName, @exists = @exists OUTPUT;
END TRY
BEGIN CATCH SET @exists = 0; END CATCH
IF @exists = 1
BEGIN
SET @db_OK = @db_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] User [' + @prinName + '] is member of [' + @checkRole + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] User [' + @prinName + '] needs [' + @checkRole + '] membership';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; ALTER ROLE ' + QUOTENAME(@checkRole) + N' ADD MEMBER ' + QUOTENAME(@prinName) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Added [' + @prinName + '] to [' + @checkRole + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not add [' + @prinName + '] to [' + @checkRole + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
FETCH NEXT FROM cur_Roles INTO @checkRole;
END
CLOSE cur_Roles; DEALLOCATE cur_Roles;
-- ---- Extra roles detection/revocation (unified, @EnforceMatrix only) ----
IF @EnforceMatrix = 1
BEGIN
DELETE FROM @userRoles;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT r.name
FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE m.name = @userName
AND r.name IN (''db_datareader'',''db_datawriter'',''db_ddladmin'',''db_owner'')';
BEGIN TRY
INSERT INTO @userRoles EXEC sp_executesql @DynSQL, N'@userName SYSNAME', @userName = @prinName;
END TRY
BEGIN CATCH END CATCH
DECLARE cur_ExtraRoles CURSOR LOCAL FAST_FORWARD FOR
SELECT RoleName FROM @userRoles WHERE RoleName NOT IN (SELECT RoleName FROM @checkRoles);
OPEN cur_ExtraRoles; FETCH NEXT FROM cur_ExtraRoles INTO @extraRole;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[REVOKE] User [' + @prinName + '] has [' + @extraRole + '] but matrix does not specify it';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_REVOKE = @db_REVOKE + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; ALTER ROLE ' + QUOTENAME(@extraRole) + N' DROP MEMBER ' + QUOTENAME(@prinName) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Revoked [' + @prinName + '] from [' + @extraRole + '] (not in matrix)'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not revoke [' + @prinName + '] from [' + @extraRole + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
FETCH NEXT FROM cur_ExtraRoles INTO @extraRole;
END
CLOSE cur_ExtraRoles; DEALLOCATE cur_ExtraRoles;
END
-- ---- Grant checks (EXECUTE, VIEW DEFINITION, SHOWPLAN) (unified) ----
DELETE FROM @checkGrants;
IF @fExec = 1 INSERT INTO @checkGrants VALUES ('EXECUTE');
IF @fViewDef = 1 INSERT INTO @checkGrants VALUES ('VIEW DEFINITION');
IF @fShowplan = 1 INSERT INTO @checkGrants VALUES ('SHOWPLAN');
DECLARE cur_Grants CURSOR LOCAL FAST_FORWARD FOR SELECT GrantName FROM @checkGrants;
OPEN cur_Grants; FETCH NEXT FROM cur_Grants INTO @checkGrant;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @exists = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_permissions dp
JOIN sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id
WHERE dp.permission_name = @grantName AND pr.name = @userName AND dp.state IN (''G'',''W'')
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@grantName SYSNAME, @userName SYSNAME, @exists BIT OUTPUT',
@grantName = @checkGrant, @userName = @prinName, @exists = @exists OUTPUT;
END TRY
BEGIN CATCH SET @exists = 0; END CATCH
IF @exists = 1
BEGIN
SET @db_OK = @db_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] User [' + @prinName + '] has grant [' + @checkGrant + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] User [' + @prinName + '] needs grant [' + @checkGrant + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; GRANT ' + @checkGrant + N' TO ' + QUOTENAME(@prinName) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Granted [' + @checkGrant + '] to [' + @prinName + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not grant [' + @checkGrant + '] to [' + @prinName + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
FETCH NEXT FROM cur_Grants INTO @checkGrant;
END
CLOSE cur_Grants; DEALLOCATE cur_Grants;
-- ---- Extra grants detection/revocation (unified, @EnforceMatrix only) ----
IF @EnforceMatrix = 1
BEGIN
DELETE FROM @userGrants;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT dp.permission_name
FROM sys.database_permissions dp
JOIN sys.database_principals pr ON dp.grantee_principal_id = pr.principal_id
WHERE pr.name = @userName
AND dp.permission_name IN (''EXECUTE'',''VIEW DEFINITION'',''SHOWPLAN'')
AND dp.state IN (''G'',''W'')
AND dp.class = 0';
BEGIN TRY
INSERT INTO @userGrants EXEC sp_executesql @DynSQL, N'@userName SYSNAME', @userName = @prinName;
END TRY
BEGIN CATCH END CATCH
DECLARE cur_ExtraGrants CURSOR LOCAL FAST_FORWARD FOR
SELECT GrantName FROM @userGrants WHERE GrantName NOT IN (SELECT GrantName FROM @checkGrants);
OPEN cur_ExtraGrants; FETCH NEXT FROM cur_ExtraGrants INTO @extraGrant;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[REVOKE] User [' + @prinName + '] has grant [' + @extraGrant + '] but matrix does not specify it';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_REVOKE = @db_REVOKE + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; REVOKE ' + @extraGrant + N' FROM ' + QUOTENAME(@prinName) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Revoked grant [' + @extraGrant + '] from [' + @prinName + '] (not in matrix)'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not revoke grant [' + @extraGrant + '] from [' + @prinName + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
FETCH NEXT FROM cur_ExtraGrants INTO @extraGrant;
END
CLOSE cur_ExtraGrants; DEALLOCATE cur_ExtraGrants;
END
-- ---- ExtraGrants processing (unified) ----
DECLARE cur_EG CURSOR LOCAL FAST_FORWARD FOR
SELECT ExtraGrants FROM @Parsed WHERE PrincipalName = @prinName AND @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0))
AND CustomRole IS NULL AND ExtraGrants IS NOT NULL;
OPEN cur_EG; FETCH NEXT FROM cur_EG INTO @extraGrants;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @extraGrants = @extraGrants + N'|';
WHILE LEN(@extraGrants) > 1
BEGIN
SET @delimPos = CHARINDEX(N'|', @extraGrants);
SET @grantStmt = LTRIM(RTRIM(LEFT(@extraGrants, @delimPos - 1)));
SET @extraGrants = SUBSTRING(@extraGrants, @delimPos + 1, LEN(@extraGrants));
IF LEN(@grantStmt) > 0
BEGIN
IF @DryRun = 1
BEGIN
IF @Verbose >= 1
BEGIN
SET @MESSAGE = '[INFO] User [' + @prinName + '] has ExtraGrant configured: ' + @grantStmt;
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; ' + @grantStmt;
BEGIN TRY EXEC sp_executesql @DynSQL;
IF @Verbose >= 1
BEGIN SET @MESSAGE = ' ExtraGrant: ' + @grantStmt; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: ExtraGrant failed in [' + @db_name + '] for [' + @prinName + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
END
FETCH NEXT FROM cur_EG INTO @extraGrants;
END
CLOSE cur_EG; DEALLOCATE cur_EG;
FETCH NEXT FROM cur_Principals INTO @prinName, @prinType;
END
CLOSE cur_Principals; DEALLOCATE cur_Principals;
-- ---- Phase 4b: Custom roles (unified) ----
DECLARE cur_CustomRoles CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT CustomRole, PrincipalName, PrincipalType, DefaultSchema FROM @Parsed
WHERE @@SERVERNAME LIKE ServerPattern AND DatabaseName = @db_name AND CustomRole IS NOT NULL;
OPEN cur_CustomRoles; FETCH NEXT FROM cur_CustomRoles INTO @cr_RoleName, @cr_Member, @cr_PrinType, @cr_Schema;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Check if SQL login exists at server level before proceeding
IF @cr_PrinType = 'SQL' AND NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @cr_Member)
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[MISS] Custom role [' + @cr_RoleName + '] member [' + @cr_Member + '] -- login does not exist on server';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_MISS = @db_MISS + 1;
END
FETCH NEXT FROM cur_CustomRoles INTO @cr_RoleName, @cr_Member, @cr_PrinType, @cr_Schema;
CONTINUE;
END
-- Aggregate flags for this custom role + member
SELECT @fReader = MAX(CAST(db_datareader AS INT)), @fWriter = MAX(CAST(db_datawriter AS INT)),
@fDDLAdmin = MAX(CAST(db_ddladmin AS INT)), @fOwner = MAX(CAST(db_owner AS INT))
FROM @Parsed WHERE CustomRole = @cr_RoleName AND PrincipalName = @cr_Member
AND @@SERVERNAME LIKE ServerPattern AND DatabaseName = @db_name;
-- Ensure Windows login exists (normal mode only, deduplicated)
IF @DryRun = 0 AND @cr_PrinType = 'WINDOWS' AND NOT EXISTS (SELECT 1 FROM @EnsuredLogins WHERE LoginName = @cr_Member)
BEGIN
INSERT INTO @EnsuredLogins (LoginName) VALUES (@cr_Member);
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = @cr_Member)
BEGIN
SET @DynSQL = N'CREATE LOGIN ' + QUOTENAME(@cr_Member) + N' FROM WINDOWS WITH DEFAULT_DATABASE=[master]';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Created login [' + @cr_Member + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not create login [' + @cr_Member + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
-- Check if the custom role exists
SET @exists = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_principals WHERE name = @roleName AND type = ''R''
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@roleName SYSNAME, @exists BIT OUTPUT',
@roleName = @cr_RoleName, @exists = @exists OUTPUT;
END TRY
BEGIN CATCH SET @exists = 0; END CATCH
IF @exists = 0
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] Custom role [' + @cr_RoleName + '] needs to be created in [' + @db_name + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; CREATE ROLE ' + QUOTENAME(@cr_RoleName) + N' AUTHORIZATION [dbo];';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Created custom role [' + @cr_RoleName + '] in [' + @db_name + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not create role [' + @cr_RoleName + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
ELSE
BEGIN
SET @db_OK = @db_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] Custom role [' + @cr_RoleName + '] exists in [' + @db_name + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
-- Check if the member user exists in the database
SET @memberExists = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_principals WHERE name = @userName AND type NOT IN (''R'')
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@userName SYSNAME, @exists BIT OUTPUT',
@userName = @cr_Member, @exists = @memberExists OUTPUT;
END TRY
BEGIN CATCH SET @memberExists = 0; END CATCH
IF @memberExists = 0
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] User [' + @cr_Member + '] needs to be created in [' + @db_name + '] (schema=' + @cr_Schema + ')';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; CREATE USER ' + QUOTENAME(@cr_Member) + N' WITH DEFAULT_SCHEMA=' + QUOTENAME(@cr_Schema) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Created user [' + @cr_Member + '] in [' + @db_name + '] (schema=' + @cr_Schema + ')'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not create user [' + @cr_Member + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
ELSE
BEGIN
SET @db_OK = @db_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] User [' + @cr_Member + '] exists in [' + @db_name + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
-- Check if member is in the custom role (only when both role and member exist or were just created)
-- In normal mode, we just created them above, so re-check @exists after potential creation
IF @DryRun = 0
BEGIN
-- Re-query role existence after potential creation
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_principals WHERE name = @roleName AND type = ''R''
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@roleName SYSNAME, @exists BIT OUTPUT',
@roleName = @cr_RoleName, @exists = @exists OUTPUT;
END TRY
BEGIN CATCH SET @exists = 0; END CATCH
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_principals WHERE name = @userName AND type NOT IN (''R'')
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@userName SYSNAME, @exists BIT OUTPUT',
@userName = @cr_Member, @exists = @memberExists OUTPUT;
END TRY
BEGIN CATCH SET @memberExists = 0; END CATCH
END
IF @exists = 1 AND @memberExists = 1
BEGIN
SET @inCustomRole = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name = @roleName AND m.name = @userName
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@roleName SYSNAME, @userName SYSNAME, @exists BIT OUTPUT',
@roleName = @cr_RoleName, @userName = @cr_Member, @exists = @inCustomRole OUTPUT;
END TRY
BEGIN CATCH SET @inCustomRole = 0; END CATCH
IF @inCustomRole = 1
BEGIN
SET @db_OK = @db_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] User [' + @cr_Member + '] is member of custom role [' + @cr_RoleName + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] User [' + @cr_Member + '] needs membership in custom role [' + @cr_RoleName + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; ALTER ROLE ' + QUOTENAME(@cr_RoleName) + N' ADD MEMBER ' + QUOTENAME(@cr_Member) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Added [' + @cr_Member + '] to custom role [' + @cr_RoleName + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not add [' + @cr_Member + '] to [' + @cr_RoleName + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
END
-- Check if the custom role has the right standard role memberships
-- Re-query @exists for role after potential creation in normal mode
IF @DryRun = 0
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_principals WHERE name = @roleName AND type = ''R''
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@roleName SYSNAME, @exists BIT OUTPUT',
@roleName = @cr_RoleName, @exists = @exists OUTPUT;
END TRY
BEGIN CATCH SET @exists = 0; END CATCH
END
IF @exists = 1
BEGIN
DELETE FROM @crCheckRoles;
IF @fReader = 1 INSERT INTO @crCheckRoles VALUES ('db_datareader');
IF @fWriter = 1 INSERT INTO @crCheckRoles VALUES ('db_datawriter');
IF @fDDLAdmin = 1 INSERT INTO @crCheckRoles VALUES ('db_ddladmin');
IF @fOwner = 1 INSERT INTO @crCheckRoles VALUES ('db_owner');
DECLARE cur_CRRoles CURSOR LOCAL FAST_FORWARD FOR SELECT RoleName FROM @crCheckRoles;
OPEN cur_CRRoles; FETCH NEXT FROM cur_CRRoles INTO @crCheckRole;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @crRoleExists = 0;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT @exists = CASE WHEN EXISTS (
SELECT 1 FROM sys.database_role_members rm
JOIN sys.database_principals r ON rm.role_principal_id = r.principal_id
JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
WHERE r.name = @stdRole AND m.name = @customRole
) THEN 1 ELSE 0 END';
BEGIN TRY
EXEC sp_executesql @DynSQL, N'@stdRole SYSNAME, @customRole SYSNAME, @exists BIT OUTPUT',
@stdRole = @crCheckRole, @customRole = @cr_RoleName, @exists = @crRoleExists OUTPUT;
END TRY
BEGIN CATCH SET @crRoleExists = 0; END CATCH
IF @crRoleExists = 1
BEGIN
SET @db_OK = @db_OK + 1;
IF @DryRun = 1 AND @Verbose >= 1
BEGIN
SET @MESSAGE = '[OK] Custom role [' + @cr_RoleName + '] is member of [' + @crCheckRole + ']';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END
END
ELSE
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[ADD] Custom role [' + @cr_RoleName + '] needs [' + @crCheckRole + '] membership';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_ADD = @db_ADD + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; ALTER ROLE ' + QUOTENAME(@crCheckRole) + N' ADD MEMBER ' + QUOTENAME(@cr_RoleName) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Added role [' + @cr_RoleName + '] to [' + @crCheckRole + ']'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not add [' + @cr_RoleName + '] to [' + @crCheckRole + '] in [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
END
FETCH NEXT FROM cur_CRRoles INTO @crCheckRole;
END
CLOSE cur_CRRoles; DEALLOCATE cur_CRRoles;
END
FETCH NEXT FROM cur_CustomRoles INTO @cr_RoleName, @cr_Member, @cr_PrinType, @cr_Schema;
END
CLOSE cur_CustomRoles; DEALLOCATE cur_CustomRoles;
-- ---- Extra user detection/drop (unified, @EnforceMatrix only) ----
IF @EnforceMatrix = 1
BEGIN
-- Build a list of all principals the matrix expects in this database
DELETE FROM @ExpectedPrincipals;
INSERT INTO @ExpectedPrincipals (PName)
SELECT DISTINCT PrincipalName FROM @Parsed
WHERE @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0));
-- Get actual non-system users from this database and compare
DELETE FROM @ExtraUsers;
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N';
SELECT Name FROM sys.database_principals
WHERE type NOT IN (''R'',''C'') AND sid IS NOT NULL AND principal_id > 4';
BEGIN TRY
INSERT INTO @ExtraUsers EXEC sp_executesql @DynSQL;
END TRY
BEGIN CATCH
IF @DryRun = 1
SET @MESSAGE = '[WARN] Could not query users in [' + @db_name + ']: ' + ERROR_MESSAGE();
ELSE
SET @MESSAGE = 'WARNING: Could not query users in [' + @db_name + ']: ' + ERROR_MESSAGE();
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END CATCH
DECLARE cur_ExtraUsers CURSOR LOCAL FAST_FORWARD FOR
SELECT UserName FROM @ExtraUsers WHERE UserName NOT IN (SELECT PName FROM @ExpectedPrincipals)
AND UserName NOT IN (SELECT ISNULL(CustomRole,'') FROM @Parsed
WHERE @@SERVERNAME LIKE ServerPattern
AND (DatabaseName = @db_name OR DatabaseName = '*'
OR (DatabaseName LIKE '*,-%' AND CHARINDEX(',-' + @db_name + ',', DatabaseName + ',') = 0))
AND CustomRole IS NOT NULL)
ORDER BY UserName;
OPEN cur_ExtraUsers; FETCH NEXT FROM cur_ExtraUsers INTO @extraUser;
WHILE @@FETCH_STATUS = 0
BEGIN
IF @DryRun = 1
BEGIN
SET @MESSAGE = '[DROP] User [' + @extraUser + '] exists in [' + @db_name + '] but is not in the matrix';
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @db_DROP = @db_DROP + 1;
END
ELSE
BEGIN
SET @DynSQL = N'USE ' + QUOTENAME(@db_name) + N'; DROP USER ' + QUOTENAME(@extraUser) + N';';
BEGIN TRY EXEC sp_executesql @DynSQL;
SET @MESSAGE = ' Dropped extra user [' + @extraUser + '] from [' + @db_name + '] (not in matrix)'; RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
END TRY
BEGIN CATCH SET @MESSAGE = ' WARNING: Could not drop user [' + @extraUser + '] from [' + @db_name + ']: ' + ERROR_MESSAGE(); RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT; END CATCH
END
FETCH NEXT FROM cur_ExtraUsers INTO @extraUser;
END
CLOSE cur_ExtraUsers; DEALLOCATE cur_ExtraUsers;
END
FETCH NEXT FROM cur_Databases INTO @db_name;
END
CLOSE cur_Databases; DEALLOCATE cur_Databases;
-- ==============================================================
-- Final output
-- ==============================================================
IF @DryRun = 1
BEGIN
-- Dry-run summary
RAISERROR('', 0, 42) WITH NOWAIT;
RAISERROR('=== DRY RUN SUMMARY ===', 0, 42) WITH NOWAIT;
SET @MESSAGE = 'Server-level: ' + CAST(@srv_OK AS VARCHAR) + ' OK, '
+ CAST(@srv_ADD AS VARCHAR) + ' to ADD'
+ CASE WHEN @srv_MISS > 0 THEN ', ' + CAST(@srv_MISS AS VARCHAR) + ' MISSING login' ELSE '' END;
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @MESSAGE = 'Database-level: ' + CAST(@db_OK AS VARCHAR) + ' OK, '
+ CAST(@db_ADD AS VARCHAR) + ' to ADD, '
+ CAST(@db_DROP AS VARCHAR) + ' to DROP, '
+ CAST(@db_REVOKE AS VARCHAR) + ' to REVOKE'
+ CASE WHEN @db_MISS > 0 THEN ', ' + CAST(@db_MISS AS VARCHAR) + ' MISSING login' ELSE '' END;
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
SET @totalChanges = @srv_ADD + @srv_MISS + @db_ADD + @db_DROP + @db_MISS + @db_REVOKE;
SET @MESSAGE = 'Total changes needed: ' + CAST(@totalChanges AS VARCHAR);
RAISERROR(@MESSAGE, 0, 42) WITH NOWAIT;
IF @EnforceMatrix = 0
RAISERROR('(Note: @EnforceMatrix = 0 -- extras will not be revoked/dropped. Use @EnforceMatrix = 1 to include.)', 0, 42) WITH NOWAIT;
END
ELSE
BEGIN
RAISERROR('=== Security configuration complete ===', 0, 42) WITH NOWAIT;
END
END TRY
BEGIN CATCH
SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment