Idea for more native collections

[R4PH1]

With the possibility of custom reports I would love to archive 2 new huge things:

• Get more insights into dependencies of databases between databases and also linked SQL servers

• including which objects have those references

• Build a custom report of detailed user access permissions per database, associated logins or groups.

• select on user our group and see all access permissions over all databases

I know that I could create custom collections, but as far as I understood those SPs need to be created on all SQL servers and kept in sync as well? Maybe those imports below would benefit others as well.

What I would like to see getting collected natively (also could be disabled per default and only be enabled on demand):

at database level (once daily):
sys.sql_expression_dependencies
sys.sql_modules
sys.objects //exclude constraints (keys) and triggers ->andWhere('type')->notIn(['C', 'D', 'F', 'PK', 'EC', 'TR']) //code from my own legacy tool
sys.triggers

at server level (once daily or less):
sys.servers (linked servers)
sys.server_triggers
sys.linked_logins

optional
msdb.dbo.sysoperators (to extend the gui to see possible notification recipients for sql jobs)

To explain further, a detailed report showing me e.g. server A hosts database X with an view referencing another database Y on server B.
I directly see all views, SPs, triggers, synonyms with their names instead of the IDs, showing me all target tables or views.
Unfortunately some linked servers use multiple dns cnames (don´t ask why please, it is what it is) so I need a way to translate them into their instances within the report but this shouldn´t be too hard to implement myself once I have the requested datasets.

Thanks already, I don´t know how much effort this is to implement on your side, but this would help a lot to gain more visibility over all servers.

[DavidWiseman]

Hi. Thanks for the suggestion. On the permissions side, there are some collections relating to server and database level permissions. They are not displayed in the GUI, but you might be able to create a custom report to display what you are interested in. This example shows how you can create a report to see sysadmin users across your SQL instances.

As you suggested, custom collections are an option. The collection procs would need to be deployed/updated on your monitored SQL instances. The app helps provide scripts for the repository database that you can tweak which helps limit the amount of effort involved in creating custom collections.

Schema snapshots might cover some of the information requested - like sys.sql_modules.

[R4PH1]

Hi, thanks David.
Yes, I am aware of those permission related collections already. They will come in great once I get into the report queries :)
I also tested all your custom reports to get a few insight how they work in detail. I will share them once I got a working version, unfortunately it´s quite busy at the moment so it will take a few weeks.

I opened the idea with hope those listed collections get included into the collector so I can avoid maintaining them manually on all our instances.

sys.sql_modules is not be required. I used this in the past to parse the text out of the definitions but this should be obsolete and could be ignored for this idea. This would also take a lot of storage.
I wanted to avoid schema snapshots over all our instances for performance and storage reasons and I don´t really need the full definitions of the modules. sys.sql_expression_dependencies comes in really great reading the references for this purpose.

The other ones would be really amazing to have so I can setup the custom reports for linked server "usage/access analysis" as well.

If you find some time to implement them I would really appreciate it a lot, but obviously no hurry. The tool is already doing an amazing job for daily checks.

[DavidWiseman]

Schema snapshots in DBA Dash are quite storage efficient. The text is stored compressed and it's hashed so that we only store a single instance of it. If you have the same proc in 1000 databases and you snapshot it every day - It's only stored once.
The snapshot process uses SMO - which can be slow at times. It's single threaded for each instance which limits the performance impact but it does mean the process can take a while to run. If possible it should be scheduled to run out of hours which will limit the impact. Databases with a very large number of objects should be excluded.

We don't use linked servers. I might still be interested in capturing some of this data like sys.servers so I can validate that no linked servers have been setup.