All we need is an easy explanation of the problem, so here it is.
I have two server users, lets say admin and notadmin. I need to test notadmin‘s permissions. I need set and test VIEW SERVER STATE permission on notadmin, but usually I have access only to database only as admin. My admin is not sysadmin, because of problem with default schema (sysadmins have always default schema dbo, but i need something else), but I can set almost any permissions I need.
I proceed from this
For testing permissions there is HAS_PERMS_BY_NAME and it works well. So I tried to impersonate notadmin. I set IMPERSONATE ANY LOGIN permission to admin, and it works well. I test it this way (on master db):
SELECT HAS_PERMS_BY_NAME('notadmin', 'LOGIN', 'IMPERSONATE') -- return 1
EXECUTE AS login = 'notadmin'
SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE') -- return 1
REVERT
Because I don’t like ANY (this way I can impersonate someone with sysadmin role), I tried set this
GRANT IMPERSONATE ON LOGIN::admin to notadmin
and then a run same query. But it failed – returned 0 and error:
Cannot execute as the server principal because the principal "notadmin" does not exist, this type of principal cannot be impersonated, or you do not have permission.
Principal have to exist and can by impersonated, with IMPERSONATE ANY LOGIN it works. So which permission I need to impersonate notadmin?
Or is there some other way how can I test some other db user permission and do not allow impersonate any other users?
Edit (16.04.2021 12:13):
I find that HAS_PERMS_BY_NAME returns same values as system views in master db. So SELECT * FROM sys.server_permissions is useless
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Method 1
GRANT IMPERSONATE ON LOGIN::admin to notadmin is telling SQL Server that you want to give the IMPERSONATE permission to the notadmin account so that it can IMPERSONATE other users. It sounds like your goal is the opposite, that you want to impersonate the notadmin account from the admin account. You already are accomplishing this when you run the SQL code EXECUTE AS LOGIN = 'notadmin' (when you’re logged in as your admin account).
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0