• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Revoking Access for All Users of a Database in SQL Server

May 9, 2016 by Andy Hayes Leave a Comment

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.

evoke access for all users in a sql server database

Related Posts:

  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…

Filed Under: All Articles, SQL Server Administration Tagged With: sql server

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 Google+

Reader Interactions

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©