Sql-server – trust the effective permissions of default database roles

permissionsrolesql serversql-server-2008-r2

I've granted membership in the db_datareader and db_datawriter roles for some testing being performed by a specific user.

Looking at the names of each role, I know what the roles do. However, I want to see where the permissions were assigned just to better understand.

To my surprise I see a blank slate for the db_datareader schema for that database. I go onto read Understanding SQL Server fixed database roles and from there I know why I cannot see anything in that properties dialog:

The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you'll have to query the membership of this group via the use of sp_helprolemember

I was aware of querying the roles which obviously works fine. So I thought this was perhaps a limitation of the GUI. I liked the permissions script from this answer and gave that a run. However, upon execution, that returns NULL for permissiontype and permissionstate which is consistent with the above quote.

All that said, are these pre-canned roles infallible? Can I assume that no one can mess around with the implicit nature of their respective permissions i.e you can't remove select rights from db_datareader behind the scenes?

Best Answer

Your question:

are these pre-canned roles infallible? Can I assume that no one can mess around with the implicit nature of their respective permissions i.e you can't remove select rights from db_datareader behind the scenes?

According to the documentation on MSDN, fixed database roles, including db_datareader and db_datawriter cannot be modified:

These roles exist in all databases. The permissions assigned to the fixed-database roles cannot be changed.

Therefore, you can rest-assured assignment of rights via these roles are fixed and cannot be modified.

Granting membership in db_datareader is the equivalent of doing GRANT SELECT ON DATABASE::[db-name] TO [principal];

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

While you may have a lot of users, it would be unusual for them to require their own views. The views should be in one schema (possibly the one owning the tables) and the users should query them by either prefixing the schemaname (eg vwowner.view) or using the

ALTER SESSION SET_CURRENT_SCHEMA=vwowner

Roles are transient. You can do a SET ROLE NONE to turn them all off. You can have multiple sessions for the same account with different roles enabled. That isn't compatible with the way that Oracle handles objects (where they are either valid or they are not; they can't be valid for some sessions and not for others).

Sql-server – List of role permissions including other roles – how can I find out the roles that belong to other roles

;WITH theRoles (member_principal_id, role_principal_id) 
AS 
(
  SELECT 
   r.member_principal_id, 
   r.role_principal_id
  FROM sys.database_role_members r 
   UNION ALL
  SELECT 
   tr.member_principal_id, 
   rm.role_principal_id
  FROM sys.database_role_members rm 
            INNER JOIN theRoles tr 
                    ON rm.member_principal_id = tr.role_principal_id
)



select  the_role=A.name
       ,A.is_fixed_role
       ,role_member=B.name
       ,B.type_desc
from theRoles tr
        INNER JOIN sys.database_principals A 
                ON tr.role_principal_id = A.principal_id
        INNER JOIN sys.database_principals B 
                ON tr.member_principal_id = B.principal_id
GROUP BY A.name
       ,A.is_fixed_role
       ,B.name
       ,B.type_desc
order by a.is_fixed_role DESC
        ,a.name

and for testing the script above:

USE [DBA]
GO
CREATE ROLE [role_1]
GO
CREATE ROLE [role_2]
GO
CREATE ROLE [role_3]
GO
CREATE ROLE [role_10]
GO
CREATE ROLE [role_101]
GO
CREATE ROLE [role_1010]
GO

sp_addrolemember @rolename='db_datareader',@membername='role_1'
go
sp_addrolemember @rolename='db_datareader',@membername='role_2'
go
sp_addrolemember @rolename='db_datareader',@membername='role_3'
go
sp_addrolemember @rolename='role_1',@membername='role_10'
go
sp_addrolemember @rolename='role_10',@membername='role_101'
go
sp_addrolemember @rolename='role_10',@membername='role_1010'
go
sp_addrolemember @rolename='role_101',@membername='role_1010'
go

Best Answer

Related Solutions

When creating views, why do users need direct object permissions if they already have the same permissions via a role

Sql-server – List of role permissions including other roles – how can I find out the roles that belong to other roles

Related Question