14

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.

14

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.

  • Would that mean that user who have db_owner membership have by default the same permissions as db_reader and db_writer? – webworm Jun 26 '14 at 16:14
  • 4
    A member of the db_owner fixed database role will have SELECT, INSERT, UPDATE, and DELETE permissions on the database. Amongst many other permissions, but yes, that's correct. – Thomas Stringer Jun 26 '14 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.