SQL Server: Generate GRANT/DENY Schema Permissions Script

I needed to temporarily block read/write access to a database for all users except 2. Problem? Hundreds of SQL logins had access, but I couldn't take the DB offline or set read-only mode since those 2 users needed full access. Disabling logins wasn't an option either—those users needed access to other production databases.

One way to accomplish this is by adding users to db_denydatareader & db_denydatawriter database level roles. Users will not be able to directly read/write to the tables or through views, even if they are owner of that table or have database owner permission.  But they can still read/write if allowed through stored procedures, functions etc. So to disallow access to data through the code you can deny execute permissions at the schema level:

DENY EXECUTE ON SCHEMA::[dbo] TO [User1];

For more information on schema permissions:

Grant-schema-permissions-transact-sql

And that is the approach I ended up taking,  by denying permissions at the schema level to all objects own by that schema. And wrote the following script to generate the SQL statements for each user excluding the two users:

That's exactly what I did: DENY EXECUTE permissions at the schema level for all objects. Here's the script that generates ready-to-execute GRANT/DENY statements for every user, excluding the 2 exceptions.

SET NOCOUNT ON;
USE [Your Database Name];

-- =============================================
-- Generate GRANT/DENY Schema Permission Scripts
-- =============================================
-- Purpose: Creates ready-to-execute GRANT/DENY statements for schema permissions
--          on ALL valid database users (excluding system accounts)
-- Usage:   Change @SchemaName and @Permission below, then execute
-- Output:  Columns with GRANT and DENY scripts for each user
-- Blog:    Generate SQL Script to Allow or Disallow Access To All Data
-- =============================================


-- CONFIGURATION - CHANGE THESE VALUES
DECLARE @SchemaName     SYSNAME = 'dbo';        -- Target schema (must exist)
DECLARE @Permission     VARCHAR(100) = 'EXECUTE'; -- Permission to grant/deny
-- Valid: ALTER, DELETE, EXECUTE, INSERT, SELECT, UPDATE, etc.


-- Valid schema-level permissions (per MS Docs)
DECLARE @ValidPermissions TABLE (PermissionName SYSNAME PRIMARY KEY);
INSERT @ValidPermissions (PermissionName) VALUES
    ('ALTER'), ('CONTROL'), ('CREATE SEQUENCE'), ('DELETE'), ('EXECUTE'),
    ('INSERT'), ('REFERENCES'), ('SELECT'), ('TAKE OWNERSHIP'), 
    ('UPDATE'), ('VIEW CHANGE TRACKING'), ('VIEW DEFINITION');

-- =============================================
-- VALIDATION
-- =============================================
-- Check if schema exists
IF SCHEMA_ID(@SchemaName) IS NULL
BEGIN
    RAISERROR('ERROR: Schema ''%s'' does not exist.', 16, 1, @SchemaName);
    RETURN;
END

-- Check if permission is valid
IF NOT EXISTS (SELECT 1 FROM @ValidPermissions WHERE PermissionName = @Permission)
BEGIN
    RAISERROR('ERROR: ''%s'' is not a valid schema permission.', 16, 1, @Permission);
    PRINT 'Valid schema permissions:';
    SELECT PermissionName + ',' FROM @ValidPermissions ORDER BY PermissionName
    FOR XML PATH('');
    RETURN;
END

-- =============================================
-- GENERATE GRANT/DENY SCRIPTS
-- =============================================
SELECT 
    dp.name AS [UserName],
    @SchemaName AS [SchemaName],
    -- GRANT script (fully qualified, ready to execute)
    'USE ' + QUOTENAME(DB_NAME()) + ';' + CHAR(13) + CHAR(10) + 
    'GRANT ' + @Permission + ' ON SCHEMA::' + QUOTENAME(@SchemaName) + 
    ' TO ' + QUOTENAME(dp.name) + ';' AS [GrantScript],
    
    -- DENY script (fully qualified, ready to execute)
    'USE ' + QUOTENAME(DB_NAME()) + ';' + CHAR(13) + CHAR(10) + 
    'DENY ' + @Permission + ' ON SCHEMA::' + QUOTENAME(@SchemaName) + 
    ' TO ' + QUOTENAME(dp.name) + ';' AS [DenyScript]

FROM sys.database_principals dp
WHERE dp.name NOT IN ('public', 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys')
  AND dp.name NOT LIKE '#%'
  AND dp.name NOT IN ('AppUser1', 'AppUser2')  -- Customize exclusions
  AND dp.is_fixed_role = 0                    -- Exclude fixed roles
  AND dp.type IN ('S', 'U', 'G')              -- SQL Users, Windows Users, Windows Groups only
ORDER BY dp.name;

Script Output: Two columns with ready-to-execute GRANT and DENY statements for every user. Copy whichever you need.

There is also Revoke option to remove a granted schema permission.

Now, at while back in the past SQL Server allowed 'ALL" keyword to grant or revoke all permissions in one go. That is no longer allowed so you will need to rerun the script for each permissions to grant or revoke. So in my this particular requirement it would be SELECT, INSERT, UPDATE & EXECUTE. To avoid that I could turn this into a stored procedure but I didn't want add complexity or use any dynamic SQL that I may have to resort for that.

Note: SQL Server used to allow ALL to grant/revoke everything at once, but that's no longer supported. You'll need to rerun the script for each permission (SELECT, INSERT, UPDATE, EXECUTE in my case). 

I could have made this a stored proc with dynamic SQL, but kept it simple to avoid complexity.

Alternative: To completely block logins, use this simpler script:

-- =============================================
-- Generate Login DISABLE/ENABLE Scripts
-- =============================================
-- Purpose: Creates ALTER LOGIN DISABLE/ENABLE statements
--          for ALL database users mapped to SQL Server logins
-- Usage:   Run in target database. Copy DISABLE scripts to block all logins.
-- Note:    Excludes Windows groups, roles, certificates - SQL logins only
-- =============================================

USE AdminDBA;  -- Change to your target database
GO

SELECT 
    sp.name AS [Login_Name],
    dp.name AS [DB_User],
    -- DISABLE script (blocks login server-wide)
    'ALTER LOGIN ' + QUOTENAME(sp.name) + ' DISABLE;' AS [Disable_Login],
    
    -- ENABLE script (restores login access)
    'ALTER LOGIN ' + QUOTENAME(sp.name) + ' ENABLE;' AS [Enable_Login]

FROM sys.database_principals dp
    INNER JOIN sys.server_principals sp
        ON dp.sid = sp.sid
WHERE dp.name NOT IN ('public','dbo','guest','INFORMATION_SCHEMA','sys')
  AND dp.name NOT IN ('AppUser1', 'AppUser2')     -- FIXED: was duplicate AppUser1
  AND sp.type = 'S'                               -- SQL Server logins only
  AND sp.is_disabled = 0                          -- Currently enabled logins only
ORDER BY dp.name;