The following sample query demonstrates how the query view can be joined to a security view. In most cases, the v_Query view will not be used in reports.

Joining Query and Security Views

The following query lists the query ID, query name, user name, and instance permissions for the user on the query object. The v_Query view is joined to the v_UserInstancePermNames security view by using the QueryID from v_Query and InstanceKey from v_UserInstancePermNames. Because there might be other secured objects with the same value as the InstanceKey (for example, MCM00001 could be a custom query or a package), the query also filters specifically for query objects by using the WHERE clause and an ObjectKey value of 7.

SELECT Q.QueryID, Q.Name AS QueryName, UIP.UserName, UIP.PermissionName

FROM v_Query Q INNER JOIN v_UserInstancePermNames UIP

ON Q.QueryID = UIP.InstanceKey

WHERE UIP.ObjectKey = 7

ORDER BY Q.Name, UIP.UserName

See Also