Azure SQL DB | GRANT VIEW DEFINITION to sys.database_role_members?

Knall 1

Trying to enable view definition on sys.database_role_members

Running this query:

GRANT VIEW DEFINITION ON OBJECT::sys.database_role_members TO [user].

Get the following error:
Granted or revoked privilege VIEW DEFINITION is not compatible with object.

Works fine on other non sys tables. Want to enable user access to view metadata on sql sys.database_role_members view.

Any Idea?

Thx

/Fredrik

Stratos Matzouranis 36 Reputation points

2020-08-26T13:08:42.64+00:00

have you tried to grant SELECT on?
Knall 1 Reputation point

2020-08-26T13:24:29.303+00:00

Yes tried the following command, GRANT SELECT ON OBJECT:: sys.database_role_members TO [user] also. Runs fine

user has access to the sys.database_role_members table (default, I think), but can only see one record. (Users own record) Need to see all records, with the least privilege access.

View DEFINITION at higher level works, but gives to much access..
Mike Ubezzi 2,776 Reputation points

2020-09-01T03:54:23.727+00:00

Hi @Knall - Did you get this resolved? Appears you are very close but if you have not configured this as desired, can you please detail what permissions you have established for the credential needing a very limited view, and I can spend some time on this. If you have resolved this, please detail the solution. Thank you!

Your answer