I’m a fan of the built-in database roles like
db_datareaderto 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
Read on for the script.