SQL Server

SQL Server Security: Database “db_datareader” Roles

The db_datareader role

The db_datareader role has the ability to run a SELECT statement against any table or view in the database. This role is often used in reporting databases where users would be coming in via a third-party reporting tool and building their own ad-hoc queries. If you need to restrict a user to only be able to read from certain tables, the db_datareader is not the right choice as it would have to be combined with the explicit use of DENY permissions on tables the user shouldn’t be able to access. A better practice would be to create a user-defined database role with the proper permissions.

One key point about the db_datareader role is that it always has the right to read all tables and views. That means if you create a new table in the database, a member of the db_datareader role has access immediately. This differs greatly from a user-defined role with which you must explicitly grant each permission. Therefore, unless you give a user-defined role permission to access a database object such as a table or view, that role can’t do so. Not only does it have access to user tables but also system tables. That means a member of the db_datareader role can execute a SELECT query against a system table even you decide to revoke public access to SELECT against these tables (keep in mind that revoking default permissions would result in an unsupported configuration so far as Microsoft is concerned).