Generate SQL Script to Allow or Disallow Access To All Data

I had a requirement to temporarily prevent access, read or write, to a particular database for all users except 2 users. There were hundreds of SQL Logins with access to this database. But since 2 users still needed full access to this database I could not simply take it offline or put into a read only mode. And keep in mind this restriction was to be temporary. I could also not simply disable their logins (script at the end of this article) as some users have access to other databases that needed to be maintained.

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:

https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16

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 o generate the SQL statements for each user excluding the two users:

/* 
-- List of valid permissions at the schema level
ALTER
CONTROL
CREATE SEQUENCE
DELETE
EXECUTE
INSERT
REFERENCES
SELECT
TAKE OWNERSHIP
UPDATE
VIEW CHANGE TRACKING
VIEW DEFINITION
Please see: https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16
*/
SET NOCOUNT ON
GO
USE <DBName>
DECLARE @schema_owner varchar(100)
DECLARE @schema_permission varchar(100) = 'EXECUTE'
SET @schema_owner = 'dbo'     
declare @valid_permissions table(valid_permission_name varchar(100))
insert into @valid_permissions
values
('ALTER'),
('CONTROL'),
('CREATE SEQUENCE'),
('DELETE'),
('EXECUTE'),
('INSERT'),
('REFERENCES'),
('SELECT'),
('TAKE OWNERSHIP'),
('UPDATE'),
('VIEW CHANGE TRACKING'),
('VIEW DEFINITION')
IF SCHEMA_ID(@schema_owner) is null
BEGIN
RAISERROR('Error: Schema %s does not exist.', 16, 1, @schema_owner)
GOTO QUIT
END
if not exists(select * from @valid_permissions where valid_permission_name = @schema_permission)
BEGIN
RAISERROR('Error: Permission (%s) is not a valid schema permission.', 16, 1, @schema_permission)
SELECT valid_permission_name FROM @valid_permissions
GOTO QUIT
END
SELECT 
name [user_name],
@schema_owner [schema_name],
'USE ' + QUOTENAME(db_name()) + ';' + 
'GRANT ' + @schema_permission + ' ON SCHEMA::' + QUOTENAME(@schema_owner) + ' TO ' + QUOTENAME(name) + ';' [Grant_Schema_Access],
'USE ' + QUOTENAME(db_name()) + ';' + 
'DENY ' + @schema_permission + ' ON SCHEMA::' + QUOTENAME(@schema_owner) + ' TO ' + QUOTENAME(name) + ';' [Deny_Schema_Access]
FROM sys.database_principals
WHERE      name not in ('public','dbo','guest','INFORMATION_SCHEMA','sys')
       AND name not in ('AppUser1','AppUser2')
       AND is_fixed_role = 0
ORDER BY name
QUIT:

The results of this script will have script to grant as well as deny the permission in two separate columns, both of which I will be needing:

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.

If you just need to prevent users from able to login altogether, you can use the following to generate the SQL script for it;

use AdminDBA
SELECT sp.name [Login_Name],
       dp.name [DB_User],
       'ALTER LOGIN ' + QUOTENAME(sp.name) +' DISABLE;' [Disable_Logins],
       'ALTER LOGIN ' + QUOTENAME(sp.name) +' ENABLE;' [Enable_Logins]
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', 'AppUser1')
ORDER BY dp.name

Generate SQL Script to Allow or Disallow Access To All Data

Rate

Share

Share

Rate