db_owner and sysadmin unexpected behavior

Question

db_owner and sysadmin unexpected behavior

SQLEngine

SSCommitted

Points: 1633
More actions
September 24, 2012 at 6:56 pm

#267233

I noticed specifically with SharePoint databases that just because an account is sa doesn't necessarily mean it's also db_owner
I know the theory, and that it SHOULD be implicitely mapped to dbo, which would give it all the db_owner rights anyway, but I think sometimes the account needs to be explicitely mapped to db_owner even though it's already sa at the sql instance level.:-)
Did anyone experience similar behaviour?

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

The dbo of a database has full rights in that database, including the ability to drop the database. Also being a member of the db_owner role offers no additonal permissions.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Suresh B. SSC-Insane Points: 22986 More actions · Answer 2

sa is member of sysadmin server role.

sysadmin role members don't need any additional role membership.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

GilaMonster (9/25/2012)
Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.

To be clear sa is implicitly the dbo of all databases, not a member of db_owner.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

SQLEngine SSCommitted Points: 1633 More actions · Answer 5

Yes, I realize that.

Thank you all for your responces.

sestell1 SSChampion Points: 10230 More actions · Answer 6

opc.three (9/25/2012)
GilaMonster (9/25/2012)
Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.
To be clear sa is implicitly the dbo of all databases, not a member of db_owner.

This is an important note, as implicit permissions are not included in inherited permissions or impersonation, which may be the source of your problem if adding in explicit permissions fixes the issue.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 7

sestell1 (9/25/2012)
opc.three (9/25/2012)
GilaMonster (9/25/2012)
Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.
To be clear sa is implicitly the dbo of all databases, not a member of db_owner.
This is an important note, as implicit permissions are not included in inherited permissions or impersonation, which may be the source of your problem if adding in explicit permissions fixes the issue.

Impersonation is not on the table. A sysadmin can impersonate any login without restriction.

Explicit membership in a Database Role could an issue is SharePoint (or a custom web part) is checking to see if a login is a member of a database's db_owner Role and not bothering to check if the login is a dbo or a sysadmin.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

opc.three (9/25/2012)
GilaMonster (9/25/2012)
Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.
To be clear sa is implicitly the dbo of all databases, not a member of db_owner.

It's both. A sysadmin login maps to dbo in all databases and dbo is a member of db_owner (it may not be the database owner, but it is always a member of db_owner)

Even if there's an explicit user mapping for the sysadmin login, any checking of Username will still return dbo.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sestell1 SSChampion Points: 10230 More actions · Answer 9

opc.three (9/25/2012)
sestell1 (9/25/2012)
opc.three (9/25/2012)
GilaMonster (9/25/2012)
Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.
To be clear sa is implicitly the dbo of all databases, not a member of db_owner.
This is an important note, as implicit permissions are not included in inherited permissions or impersonation, which may be the source of your problem if adding in explicit permissions fixes the issue.
Impersonation is not on the table. A sysadmin can impersonate any login without restriction.
Explicit membership in a Database Role could an issue is SharePoint (or a custom web part) is checking to see if a login is a member of a database's db_owner Role and not bothering to check if the login is a dbo or a sysadmin.

If you impersonate an account that does not have explicit rights within a database, you will not have any rights within that database, even if both accounts are members of sysadmin. 😉

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 10

GilaMonster (9/25/2012)
opc.three (9/25/2012)
GilaMonster (9/25/2012)
Any member of the sysadmin role has all permissions across the instance, is implicitly db_owner of all databases and cannot be denied anything.
To be clear sa is implicitly the dbo of all databases, not a member of db_owner.
It's both. A sysadmin login maps to dbo in all databases and dbo is a member of db_owner (it may not be the database owner, but it is always a member of db_owner)

My only point was that being a member of sysadmin does not imply there is a user in the db_owner Role named 'sa.' If there is something checking for that by name it would fail to find a row. Same is true for sysadmins not named sa but that is a bit more obvious because we create logins and database users more often then we ever have to create sa and dbo.

If in fact being a member of the sysadmin Server Role and the Database Role db_owner is required by some aspect of SharePoint, either Microsoft or third-party, and that is being checked by name then there is some smelly code out there causing that problem.

SELECT *

FROM sys.server_principals sp

JOIN sys.database_principals dp ON sp.sid = dp.sid -- < this join is needed intead of going off name

JOIN sys.database_role_members drm ON dp.principal_id = drm.member_principal_id

JOIN sys.database_principals dp_role ON drm.role_principal_id = dp_role.principal_id

WHERE sp.name = 'sa'

AND dp_role.name = 'db_owner';

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

sestell1 (9/25/2012)
If you impersonate an account that does not have explicit rights within a database, you will not have any rights within that database, even if both accounts are members of sysadmin. 😉

If you impersonate a sysadmin login (as in EXECUTE AS LOGIN = <sysadmin login>), you are a sysadmin with all permissions in all DBs, regardless of whether there's a mapping. I just tested this on 2008 with a new sysadmin account that had no explicit database mappings (and hence no explicit database permissions)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sestell1 SSChampion Points: 10230 More actions · Answer 12

GilaMonster (9/25/2012)
sestell1 (9/25/2012)
If you impersonate an account that does not have explicit rights within a database, you will not have any rights within that database, even if both accounts are members of sysadmin. 😉
If you impersonate a sysadmin login (as in EXECUTE AS LOGIN = <sysadmin login>), you are a sysadmin with all permissions in all DBs, regardless of whether there's a mapping. I just tested this on 2008 with a new sysadmin account that had no explicit database mappings (and hence no explicit database permissions)

It looks like it will work from within the same database, but won't map across databases. Try accessing a table in a database other than the default database you ran the "EXECUTE AS" from.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

opc.three (9/25/2012)
My only point was that being a member of sysadmin does not imply there is a user in the db_owner Role named 'sa.'

No, there doesn't have to be (I personally never map sysadmins to database users), and even if there is, USER_NAME() will never return 'sa', it'll return 'dbo'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sestell1 SSChampion Points: 10230 More actions · Answer 14

Hmm, actually I had a mapping in place.

With the explicit mapping removed, it doesn't work for me even within the same database.

-- Logged in as sa

USE master;

SELECT COUNT(*) FROM master.dbo.mytesttable;

EXECUTE AS USER = '<Other account in sysadmin role>';

SELECT COUNT(*) FROM master.dbo.mytesttable;

REVERT;

I get this:

(1 row(s) affected)

Msg 229, Level 14, State 5, Line 6

The SELECT permission was denied on the object 'mytesttable', database 'master', schema 'dbo'.

So I'm a bit puzzled as to how you were able to see impersonation work with implicit permissions. :unsure: