List role users as a standard user

Question

Normal user can not , because of insufficient privileges , list database users that belong to a certain role.

Execute as does not seem to work (in a stored procedure).

Listing only returns current user.

My question:

How can I have a normal user (that is granted execute permission on the stored procedure in question) obtain the list of all role users?

score 2 · Accepted Answer · answered Dec 29 '17 at 14:13

How about encapsulating high-privilege functionality in a stored procedure:

CREATE PROCEDURE dbo.GetRoleList
WITH EXECUTE AS OWNER
AS
BEGIN
   SELECT ...
END
GO

Of course, you should create the procedure with a user that has the rights to list role users. When the regular user executes the procedure, the owner's rights will be used instead (thus allowing to execute "elevated" queries).

Check this post for more details.

List role users as a standard user

1 Answers1