Revoking Access for All Users of a Database in SQL Server

I love Management Studio for SQL Server but sometimes it just doesn’t cut the mustard. I had a scenario recently where I needed to revoke access for all users from a database. I could delete them all but that would be more effort than necessary. I just wanted to deny access to a specific database.

To my knowledge, there is not a quick way to do this and so I had to write some T-SQL to do it.

You can revoke a database user using syntax REVOKE CONNECT, for example

REVOKE CONNECT FROM my_user;

What if you have many SQL Server users and you want to stop all of them accessing the database?

Identify them using this query from sysusers. In my case I am using an additional filter to pull out users with a specific prefix.

SELECT name FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

In my case, this returns:

name
-----------
user_1
user_2
user_3

(3 row(s) affected)

To do this, I made a small modification to this query using some concatenation and square brackets in case of weird user names which contain hyphens:

SELECT 'REVOKE CONNECT FROM [' + name + '];' FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

This returned and I know have a series of statements that I can run as one batch:

-------------------------------
REVOKE CONNECT FROM [user_1];
REVOKE CONNECT FROM [user_2];
REVOKE CONNECT FROM [user_3];

(3 row(s) affected)

Copy and paste these into a new query and execute them. Test that access has been revoked either by looking in Management Studio or querying sysusers for hasdbaccess = 1 again.

SELECT COUNT(*) FROM sysusers WHERE hasdbaccess = 1 AND name LIKE 'user{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

If you’re looking in Management Studio at the users, then a red arrow indicates that the user has been revoked.