What does role db_owner allow

Question

I have been trying to troubleshoot a login that is not able to view certain tables in a SQL Server 2012 database. In doing so I find I don't quite understand what membership in the db_owner role allows. I can understand the other roles such as db_datareader and db_datawriter but I remain confused as to what db_owner allows.

Answer 1

Please see the reference on BOL for Database-Level Roles:

db_owner

Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

The easiest way to see all of the permissions is to use the sys.fn_my_permissions() function. First verify you are a member of db_owner:

select
    rol.name
from sys.database_principals mem
inner join sys.database_role_members drm
on drm.member_principal_id = mem.principal_id
inner join sys.database_principals rol
on drm.role_principal_id = rol.principal_id
where mem.principal_id = user_id();

Now list out all of the effective database permissions:

select *
from sys.fn_my_permissions(null, 'database');

That should be granular enough for your purposes. Something to note, sys.fn_my_permissions() returns the effective permissions, so think of it as an aggregation.