Create procedure test.access
      @principal_id int
      with execute as owner
      as
      set nocount on;
         begin
            select * from sys.database_principals where principal_id = @principal_id
         end;

I create the above procedure and execute but it returns nothing. However if I declare @principal_id as a variable, set it then run this code outside a procedure it returns the correct rows.

Why isn't it working inside the procedure?

CodePudding user response：

If the owner of the test schema does not have elevated permissions, then the rows that can be seen in sys.database_principals will be limited. From the documentation

Any user can see their own user name, the system users, and the fixed database roles. To see other users, requires ALTER ANY USER, or a permission on the user. To see user-defined roles, requires ALTER ANY ROLE, or membership in the role.

In the following script I create the user test, as well as a schema test. I make the test user the owner of the test schema.

I then create a procedure in that schema with execute as owner. It will therefore execute in the context of the test user.

But the test user does not have ALTER ANY USER permission, so the rows visible in sys.database_principals are limited:

create login test with password = 'test';
create user test for login test;
go

create schema test authorization test;
go

create procedure test.access with execute as owner as 
begin   
   select * from sys.database_principals;
end
go

exec test.access; -- returns a limited set principals

If I grant the test user the alter any user permission and then re-execute the procedure, I will get all of the users:

grant alter any user to test;

exec test.access; -- returns all users (but not user defined roles)

grant alter any role to test;

exec test.access; -- returns everyone and all roles

If the test schema is currently not owned by the correct user, you can change that:

alter authorization on schema::test to [user];