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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy