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:
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