SQL: Stop using db_datareader and db_datawriter

SQL Server has a number of built-in database roles. The ones I see used the most are db_datareader and db_datawriter. They let you either read from all tables or read and write from all of them. Curiously, what has never been supplied is a db_procexecutor.

Developers and administrators who set up the use of these permissions think they are at least not using dbo or sa and while that’s true,

please stop using these roles!.

Why not use the built-in roles?

Whenever you use a built-in role, you are assigning a bucket of permissions to a user. But you have to assign them a bucket that’s at least as big as what they need. Unfortunately, you are often assigning them more permissions than what they need. This doesn’t follow the principle of least privilege which you should be following.

Even in the case of db_datareader, do they really need to be able to read every table in the database? Or for the case of db_datawriter, to be able to read and write every table in the database? That’s pretty unlikely.

What should I use?

Instead of using built-in roles, create a user-defined role that has the minimum permissions required for someone’s tasks, or for an application’s tasks. Then add them as a member.

A bonus of doing this, is that you can then add additional permissions that might be needed, like executing a procedure. You can also make it very clear why that role exists, via naming conventions, or by using extended properties.

2025-12-16