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