Delete non-built-in roles, schemas, and users from a SQL Server database

  sqlserver

I’m a fan of the built-in database roles like db_datareader to standardise & simplify permissions (sorry Dr. Greg Low!) and recently I needed to do just that in a database created using SQL Server 2000, and remove old defaults and a lot of custom roles, schemas and users.

I wrote the set of queries below to generate scripts to remove non-built-in roles, schemas and users, when compared to the model database on a new SQL Server 2019 server.

After running the script generated by the queries, I added back users and gave appropriate roles (like db_datareader).

The queries are definitely quick-and-dirty and I’ve hard-coded a list of built-in database roles by name, as well as (conveniently) ignoring application roles, keys and certificates. I’ve only tested this against SQL Server 2019 - but hopefully someone else will find it useful.

Thomas’s “but it worked for me” disclaimer: before using any code you find on the internet, especially on this blog, take time to understand what the code does and test, test, test. I’m not responsible for damage caused by code from this blog, and don’t offer any support or warranty.


--generate scripts to delete non-built-in roles, schemas, and users from a SQL Server database
--based on built-in roles, schemas and users in SQL Server 2019
--helpful if inheriting legacy databases or for non-production testing
--note these queries only script the delete statements - for best results, run
--with "Results to Text" for better copy-pasting

--script drop statements for all non-standard security in a database
--need to, in order:
-- • remove users from non-standard roles
-- • delete non-standard roles
-- • delete non-standard schemas
-- • delete non-standard users

SET NOCOUNT ON

--help
SELECT  [--script delete of non-built-in security] = N'--statements below are intended to be tested and run separately, not all at once'

--get non-standard roles into temp table
DECLARE @temp_Roles TABLE (
    [principal_id] INT NOT NULL,
    [name] SYSNAME NOT NULL PRIMARY KEY
)
INSERT INTO @temp_Roles
SELECT  [principal_id], [name]
FROM    sys.database_principals
WHERE   --roles
        [type] = 'R' AND
        --ignore in-built roles with identifiers 4 and lower e.g. public
        [principal_id] > 4 AND
        --ignore system roles e.g. db_owner, db_datareader etc.
        --need to match on hard-coded names as may have identifiers higher than 16,000
        [name] NOT IN (
         N'db_accessadmin', N'db_backupoperator', N'db_datareader', N'db_datawriter',
         N'db_ddladmin', N'db_denydatareader', N'db_denydatawriter', N'db_owner',
         N'db_securityadmin'
        )

--if we got any non-standard roles, script remove members then delete each role
IF 0 < (SELECT COUNT(*) FROM @temp_Roles) BEGIN
    --need more complex query (similar to that generated by SSMS on deleting role) as
    --remove users from role is by name, not identifier
    SELECT  [--remove members from roles] = N'ALTER ROLE ' + QUOTENAME(R.[name], N'[') + N' DROP MEMBER ' + QUOTENAME(U.[name], N'[')
    FROM    sys.database_role_members DRM INNER JOIN
                --roles
                @temp_Roles R ON
                    DRM.[role_principal_id] = R.[principal_id] INNER JOIN
                --users
                sys.database_principals U ON
                    DRM.[member_principal_id] = U.[principal_id]
    ORDER BY R.[name], U.[name]
    --script delete of roles
    SELECT  [--delete roles] = N'DROP ROLE ' + QUOTENAME([name], N'[')
    FROM    @temp_Roles
    ORDER BY [name]
END

--script delete of non-standard schemas
SELECT  [--delete schemas, will fail if any objects owned by schema, requires further manual investigation] = N'DROP SCHEMA ' + QUOTENAME([name], N'[')
FROM    sys.schemas
WHERE   --ignore built-in schemas with identifiers 4 and lower
        [schema_id] > 4 AND
        --ignore system schemas e.g. db_owner, db_datareader etc.
        --need to match on hard-coded names as may have identifiers higher than 16,000
        [name] NOT IN (
         N'db_accessadmin', N'db_backupoperator', N'db_datareader', N'db_datawriter',
         N'db_ddladmin', N'db_denydatareader', N'db_denydatawriter', N'db_owner',
         N'db_securityadmin'
        )
ORDER BY [name]

--script delete of non-standard users
SELECT  [--delete users] = N'DROP USER ' + QUOTENAME([name], N'[')
FROM    sys.database_principals
WHERE   --not roles
        [type] != 'R' AND
        --ignore built-in users (e.g. dbo, guest, INFORMATION_SCHEMA) with identifiers 4 and lower
        [principal_id] > 4
ORDER BY [name]

--remind to check for "orphaned" users & logins once done
SELECT  [--check for orphaned users] = N'EXEC sp_change_users_login ''Report'''