Read-Only View for DBADash

[sqlrebel]

Hi there, the DBA's in our company have been using DBADash for the past year, its a brilliant monitoring tool, the company would not like to roll out the GUI to a few non-DBA team leads in the company. It would be great if thay could have access to the GUI without having any permissions to modify anything such as adding/removing instances, acknowledging alerts, ect. Is there any plan to provide a read-only view? It may be beyond the scope of what DBA Dash was intended for but just asking anyways. Thanks.

[elshelto]

I believe what you want is already possible. I have shared the GUI with a few non-dba resources in our IT dept. This is from the Security page in Docs...

Security

"DBA Dash GUI
To use the DBA Dash GUI, users only need access to the DBA Dash repository database. No access is required to the monitored instances. To grant the minimum permissions to run the DBA Dash GUI, add the user the the App role in the DBA Dash database. This grants the user SELECT and EXECUTE permissions to the database.

The ManageGlobalViews role can be used to allow the user to save their customized metrics dashboards for all users. They will also have access to delete shared dashboards. Users with db_owner will also have the same access. Otherwise the user can still create their own dashboards."

One other thing to mention is if you have reports you also need to do this:

Create Custom Reports

"To run a custom report a user needs to have EXECUTE permissions on the stored procedure. If a user doesn’t have EXECUTE permissions, the report won’t be visible and the user won’t be able to run the report. You can add a user to the RunUserReports role to grant access to all custom reports."

[sqlrebel]

Hi @elshelto,
Many thanks for taking the time to post an answer.
I have looked into the App role, but this gives execute permission on the dbo schema, which means that users have update and delete privileges. For example if a SQL Agent Job has failed and an Alert is present, I don't want my read-only users to be able to ack this alert so that it is no longer visible, similarly I don't want them being able to change thresholds or change the names of instances, I would ideally have a read-only view.
As I mentioned I know this is probably beyond the scope of what DBA Dash was intended for as a 'DBA' monitoring tool. For now to get around this I have grant execute permission on the dbo schema and granted deny permission on all procs that update or delete. Its not ideal but works for now.
Thanks.

[DavidWiseman]

I think this will get you over 95% of the way there. If you use it let me know how you get on and if you need to grant any additional permissions. I might look to include it in the dacpac.

CREATE ROLE AppReadOnly
GRANT SELECT ON SCHEMA::dbo TO AppReadOnly
GRANT SELECT ON SCHEMA::DBADash TO AppReadOnly
GRANT EXEC ON TYPE::dbo.IDs TO AppReadOnly

/*
Items below generated using:
SELECT CONCAT('GRANT EXECUTE ON ',QUOTENAME(SCHEMA_NAME(schema_id)),'.',QUOTENAME(name),' TO AppReadOnly')
FROM sys.objects
WHERE name LIKE '%_Get' 
AND type = 'P'
AND SCHEMA_NAME(schema_id) IN('dbo','DBADash')
UNION ALL
SELECT CONCAT('GRANT SELECT ON ',QUOTENAME(SCHEMA_NAME(schema_id)),'.',QUOTENAME(name),' TO AppReadOnly')
FROM sys.objects
WHERE type = 'IF'
AND SCHEMA_NAME(schema_id) = 'dbo'
*/

GRANT EXECUTE ON [dbo].[DatabaseDDLCompare_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[ResourceGovernorConfiguration_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabaseID_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabaseMirroring_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[ResourceGovernorConfigurationHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabaseMirroringSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[RunningQueries_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[RunningQueriesForSession_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabaseQueryStoreOptions_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[RunningQueriesServerSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabaseQueryStoreOptionsSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[RunningQueriesSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Databases_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabasesAllInfo_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabasesByInstance_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[SessionWaits_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DatabasesWithDDLSnapshot_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DataRetention_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[SessionWaitsSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[SlowQueriesDetail_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBConfiguration_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBConfigurationHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBFiles_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[SlowQueriesSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[SQLPatching_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Summary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBFileSnapshot_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBFileThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[SysConfigHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[TagReport_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Tags_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBObjects_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[TempDBConfig_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBOptionsHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[TraceFlagHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBSchemaAtDate_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[TraceFlags_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBSpace_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DBSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DDL_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DDLHistoryForObject_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[TableSize_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Waits_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DDLSnapshotDates_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[WaitsSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DDLSnapshotDiff_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DDLSnapshotInstanceSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DDLSnapshots_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DriveLetters_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[TableSizeHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Drivers_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Drives_Get] TO AppReadOnly
GRANT EXECUTE ON [DBADash].[User_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DriveSnapshot_Get] TO AppReadOnly
GRANT EXECUTE ON [DBADash].[SavedViews_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DriveThreshold_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[DriveThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[FileGroup_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Hardware_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[HostUpgradeHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[IdentityColumns_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureDBPerformanceSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[InstanceInfo_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[InstancesWithDDLSnapshot_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[InstanceTags_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Instances_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[InstanceUptimeThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[PerformanceSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[InstanceVersionInfo_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[IOStats_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Settings_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[IOSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobCategories_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobDDLHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[IdentityColumnThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Jobs_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobStats_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobStatsSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobStep_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobSteps_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[JobTimeline_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[LastBackup_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[LastGoodCheckDB_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AgentJobs_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AgentJobThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[LastGoodCheckDBThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Alerts_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AlertsConfig_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[LogRestoreThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AvailabilityGroup_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AvailabilityGroupSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Logshipping_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureDBElasticPoolHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[LogShippingSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[MemoryClerkUsage_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[MemoryConfig_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureDBElasticPoolResourceStats_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[MemoryCounters_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureDBPoolSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[MemoryDumpThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureDBResourceGovernance_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[RunningJobs_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[MemoryUsage_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[RunningQueriesForJob_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureDBResourceStats_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[AzureServiceObjectivesHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Backups_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[BackupSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[ObjectExecutionStats_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[BackupThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[ObjectExecutionStatsSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Blocking_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[ObjectType_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[BlockingSnapshots_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[OSLoadedModules_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[BlockingSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[BuildReference_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CollectionDates_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CollectionDatesThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[OSLoadedModulesStatus_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CollectionErrorLog_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[OSLoadedModuleSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Configuration_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Counters_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[PerformanceCounter_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CounterThresholds_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[PerformanceCounterSummary_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CPU_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CustomReport_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CustomCheck_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CustomCheckContext_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[Corruption_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CustomChecksHistory_Get] TO AppReadOnly
GRANT EXECUTE ON [dbo].[CustomCheckTest_Get] TO AppReadOnly
GRANT SELECT ON [dbo].[DateGroupingMins] TO AppReadOnly
GRANT SELECT ON [dbo].[DBSchemaAtDate] TO AppReadOnly
GRANT SELECT ON [dbo].[DBSnapshotDiff] TO AppReadOnly
GRANT SELECT ON [dbo].[Histogram_CPU] TO AppReadOnly
GRANT SELECT ON [dbo].[MillisecondsToHumanDuration] TO AppReadOnly
GRANT SELECT ON [dbo].[ParseFileName] TO AppReadOnly
GRANT SELECT ON [dbo].[RunningQueriesBlockingHierarchy] TO AppReadOnly
GRANT SELECT ON [dbo].[RunningQueriesBlockingRecursiveStats] TO AppReadOnly
GRANT SELECT ON [dbo].[SecondsToHumanDuration] TO AppReadOnly
GRANT SELECT ON [dbo].[SplitStrings] TO AppReadOnly
GRANT SELECT ON [dbo].[SplitWaitResource] TO AppReadOnly
GRANT SELECT ON [dbo].[SQLVersionName] TO AppReadOnly
GRANT SELECT ON [dbo].[AzureDBMasterInstance] TO AppReadOnly
GRANT SELECT ON [dbo].[InstancesMatchingTags] TO AppReadOnly
GRANT SELECT ON [dbo].[TagValue] TO AppReadOnly

[sqlrebel]

Hi David, Thanks for getting back to me with that proposed solution. I did some testing on this and from what I can see if fills all the needs that our read-only viewers would require. One slight addition I would add is execute permission on the DBADash.User_Upd, as we deal a lot in EST time it would be useful for viewers to be able to save their own time zone preference.
If at some point that role can be included that would be great in the mean-time I will create it manually. Thanks again.

[DavidWiseman]

Thanks for the feedback. I'll probably look to have the role created in the dacpac at some point.

[DavidWiseman]

AppReadOnly role was included in 3.5