We use application roles in our application for accessing SQL Server. We call sp_setapprole to set the app role before we do any other queries from the database.
We need to select from sys.dm_tran_commit_table view, for which we need to grant select permissions on this view to the app role.
Executing
GRANT SELECT ON [sys].[dm_tran_commit_table] TO [AriaEvents]
leads to the error
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.
However, as the application role is created in a specific database and not in master, the above cannot be executed in the master database.
How can we grant permissions to an application role to be able to select on [sys].[dm_tran_commit_table]?
Thanks
- Tried to grant select permissions to the app role for [sys].[dm_tran_commit_table]
- Tried to create another view in the
dboschema which doesselect * from [sys].[dm_tran_commit_table]and grant permissions to that view to the application role. - Permission grant succeeds, but the select does not work and no rows are returned.
