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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂