0

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

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).

2
  • Thanks for help,.. I am complete idiot. There was necessary only second pair of eyes. Apr 16 at 10:57
  • @VítBednář lol no problem! You're definitely not an idiot, the security stuff in SQL Server can be a little confusing at first, and a second set of eyes can always help any problem. 🙂 Best of luck!
    – J.D.
    Apr 16 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.