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%';
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%';
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%';
If you’re looking in Management Studio at the users, then a red arrow indicates that the user has been revoked.