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%';

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.

evoke access for all users in a sql server database

It's only fair to share...Share on FacebookShare on Google+Tweet about this on TwitterShare on LinkedInPin on PinterestBuffer this pageShare on RedditShare on StumbleUpon
About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on

Speak Your Mind

*


*