db_owner and sysadmin unexpected behavior

Question

db_owner and sysadmin unexpected behavior

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

sestell1 (9/25/2012)
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.

Select runs, no errors, returns expected data.

Are you taking about impersonating a sysadmin login, or impersonating a database user that is mapped from a sysadmin login? Impersonating a sysadmin login will make you sysadmin, impersonating a database user (no matter what that user's login has as permissions) will only get you permissions in the DB that you impersonate in.

Difference between EXECUTE AS LOGIN = ... and EXECUTE AS USER =

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

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

sestell1 (9/25/2012)
So I'm a bit puzzled as to how you were able to see impersonation work with implicit permissions. :unsure:

You're impersonating a database user. You are not impersonating the sysadmin login that has implicit permissions in all DBs. You can impersonate at 2 levels, login or user. Only logins have sysadmin permissions (because they're at the server level), database users are confined to a database and have at best db_owner.

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: 182279 More actions · Answer 3

GilaMonster (9/25/2012)
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'

Absolutely. We know this because if we create a new database there is always a user for sa named dbo and that user is always a member of db_owner. And, you cannot screw around with the dbo user, i.e. I continue to agree with you on all points.

I am just trying to present a possible explanation for why there might be code that works when a Login not named sa that is in the sysadmin Server Role has a Database User that belongs to a db_owner Database Role, yet does not work when the that Login does not have a Database User part of the db_owner Database Role.

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

sestell1 SSChampion Points: 10230 More actions · Answer 4

GilaMonster (9/25/2012)
You're impersonating a database user. You are not impersonating the sysadmin login that has implicit permissions in all DBs.

I knew I must have been missing something.

Thank you!!!