Developer: how to get reporting data from about 20 databases?

Question

I'm a junior developer in our team. In one project we have about twenty databases in one SQL Server instance. We have db_owner rights to these databases.

My intention is to monitor certain things from these databases (e.g. file size). But because we don't have sysadmin rights, we don't have all those management tools for these databases. Now, my solution is to write "reporting scripts" using stored procedures. These sp's use sp_foreachdb extensively. My question is that is this a good approach for this problem? What other methods/solutions there is available?

Answer 1

I'm not sure if this is any better but you could setup the 21 database that has a table the tracks the last time an interesting table in one of the other 20 databases got updated. If you also tracked in this table the last time you ran your report you would easily know what database have changed since your last reporting cycle.

Not sure how your tables are getting updated and if you could modify an update stored procedure to mark your 21st database or not - perhaps a insert/update trigger?

Answer 2

It sounds like you're trying to come up with a technical solution to a political problem. If you don't have rights to monitor the things you need to monitor, there's not going to be a good enough solution. Just because you monitor file size isn't enough - you also want to know the free space on the drive, and you won't be able to retrieve that without more permissions.

I'd ask the sysadmins if you can give them a stored proc that will be run with sysadmin permissions. They can review the code, make sure you're not doing anything nefarious, and then sign that stored proc with a certificate. They can grant you the rights to run the stored proc (but not alter it) even though you're not in the sysadmin role, and you'll get the results.

Another approach is to give them T-SQL code that they can implement as a scheduled SQL Server Agent job that sends you the results. You get the data, but not the rights.

I'm leery of the sp_msforeachdb approach because if you're not a sysadmin, you're probably not always going to have rights to every database on the system either. If they implement any third party monitoring tools or share the server with more departments, your sp_msforeachdb code may fail.