GRANT SELECT ON sys.server_principals to usersys role. How?

Question

I can't find an answer to this. Problem is that I need to use master database and then I don't know how to specify that I am granting this select to a role in another database. When using "use master" it doesn't work because the principal is in another database and you can't add e.g. Database.dbo.role prefixes to principles. How do I do that? Even granting through SSMS UI doesn't work.

I need this: GRANT SELECT ON Syf.sys.server_principals to Syf.dbo.usersys

What am I missing? Am I thinking about this in a wrong way?

Even when I try it with the user on master database and call "execute as user = 'user'" and then select from sys.server_principals it still return only a few records. I apparently don't understand how these permissions work, it's beyond my logic. It seems there are some other objects that I need to grant permissions for.

I need to use "with execute as 'privilegedUser'", when I do that then we are in user context of that SP database and that user can't have permission to access sys.server_principals.? I need this because that SP deletes user and login if login with the same name exists. The reason I need to execute with the privileged user is because my database has multitenancy and every user is bound to one TenantId and when he or other user goes to delete the user, security policy complains that he has no right for that.

I found a way to do this, I have to grant select to sys.server_principals to guest user on master db, that gives guest some more privileges.

Answer 1

I've figured out a solution where I don't need to use "with execute as owner" in this first procedure, but in the second one that this first one calls. In the first one, I can select everything I need from sys tables and pass the info to a second one that has "with execute as owner" which is in the schema forbidden to a user.

Even better solution:

 alter trigger [dbo].[AfterInsertUpdateTenant_Korisnici]
    on [dbo].[Korisnici]
    with execute as owner
    for insert 
    as 
    execute as user = original_login();
    declare @TenantId int = dbo.GetCurrentTenantId();
    revert;

    update dbo.Korisnici
    set TenantId = @TenantId
    from Inserted i
    where dbo.Korisnici.Id = i.Id;