Disable All Users in a Database

Yesterday, I did a point-in-time restore of an Azure SQL Managed Instance database into a new copy of the database.  This copy will be used as backup as we didn't want to have a non-TDE backup laying around.  We don't have a custom managed keys in place yet.

In order to restrict access to the newly created DB, we wanted to disable all users from the database.  Here's the script I used to do that:

SELECT
    'REVOKE CONNECT FROM [' + name + '];',
    name AS username,
    create_date,
    modify_date,
    type_desc AS type,
    authentication_type_desc AS authentication_type
FROM
    sys.database_principals
WHERE
    name NOT IN ('dbo','sys','INFORMATION_SCHEMA','guest')
    AND type_desc NOT IN ('DATABASE_ROLE')
ORDER BY
    username;

 

I hope this helps someone.  Have a great day!