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?
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];