November 20, 2024 at 11:07 am
Is this even possible ?
Tried with grant but to no avail.
[sys].[database_role_members] and [sys].[database_principals] can not be accessed even with grant .
November 20, 2024 at 2:11 pm
Permissions to the DMV's are detailed on their respective documentation page.
sys.database_principals
Permissions
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.
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-principals-transact-sql?view=sql-server-ver16#permissions
sys.database_role_members
Permissions
Any user can view their own role membership. To view other role memberships requires membership in the db_securityadmin fixed database role or VIEW DEFINITION on the database.
The visibility of the metadata in catalog views is limited to securables that a user either owns, or on which the user was granted some permission. For more information, see Metadata Visibility Configuration.
https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql?view=sql-server-ver16#permissions
So unless you can grant these extra permissions, the use of the DMV's will not be possible.
November 20, 2024 at 2:15 pm
Now what you could do is to write a proc which does it then ties it to a certificate which has the access.
This is similar to how you can give access to all for procedures like sp_whoisactive.
https://whoisactive.com/docs/28_access/
If you follow something similar to this, and proxy the access down using certs and logins and signed modules.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply