Is this even possible?

I tried with grants to the role. I tried with views but nothing works. sp_getrolemember is no longer available in SQL Server 2022.

Is there a way to make normal role members view other role members in the same database without giving them additional powers?

Is this even possible?

I tried with grants to the role. I tried with views but nothing works. sp_getrolemember is no longer available in SQL Server 2022.

Is there a way to make normal role members view other role members in the same database without giving them additional powers?

• sql-server

• To see data in sys.database_principles for others the USER needs the ALTER ANY USER or ALTER ANY ROLE permissions respectively for users and roles. – Thom A Commented Nov 20, 2024 at 12:06

2 Answers 2

You could re-create sp_getrolemember with your own stored procedure. Here is how:

1. Create a table of members, roles and types:

    CREATE PROCEDURE dbo.GetRoleMembers
    @RoleName NVARCHAR(128)
    AS
    BEGIN
        SELECT 
            r.name AS RoleName,
            u.name AS MemberName,
            u.type_desc AS MemberType
        FROM 
            sys.database_role_members rm
        INNER JOIN 
            sys.database_principals r ON rm.role_principal_id = r.principal_id
        INNER JOIN 
            sys.database_principals u ON rm.member_principal_id = u.principal_id
        WHERE 
            r.name = @RoleName;
    END;

2. Let's say you want only 'mary' to access this. You can remove public access: DENY EXECUTE ON dbo.GetRoleMembers TO PUBLIC;

3. Grant view permissions to mary: GRANT EXECUTE ON dbo.GetRoleMembers TO Mary;

Lmk if that helps!

You must grant them View on the database.