Reasoning Behind SQL Monitor Needing db_owner on TempDb

mattg

Hi There,

I wanted to ask about the reasoning for explicitly creating tables inside of tempdb on SQL Server instances being monitored, and the requirement for the login SQL Monitor is using to connect to the monitored instances to have db_owner permissions on tempdb.

Would it be possible to remove this requirement in a future version of SQL Monitor?

This is troublesome primarily because tempdb permissions are removed on instance restart, as tempdb is recreated on every start of SQL Server. This can be burdensome to manage when you have dozens of SQL Server instances being monitored. Though this can be automated with SQL Agent jobs, it seems a better solution would be to remove the requirement for this permission level.

Additionally, its traditionally not been considered a good practice to explicitly create permanent (non-temp) tables in SQL Server. It looks like SQL monitor creates two temp tables and two regular tables in tempdb.

The two temp tables are created:
##redgate_sqlmonitor_querywaitstats_servernamewashere
##redgate_sqlmonitor_querywaitstats_info_servernamewashere

However these two tables are permanent:
redgate_sqlmonitor_topqueries_b_servernamewashere
redgate_sqlmonitor_topqueries_a_servernamewashere

Russell D

The reason we need to create tables inside tempdb is because we need to use some tables that are longer lived than global temp tables, and also need access to change the schema. I'm afraid that it's very unlikely this requirement will change in the future.

pholden

Wouldn't the correct way to do this be to add your own database to the instance?

Russell D

Well, as a basic answer, that would require us being able to create a database, and for obvious reasons people don't want Monitor doing that.

This problem does need to be solved for Azure DB however, which we're working on now, so we'll be investigating other solutions as well.

pholden

Hi Russell,

Thanks for the response. Maybe it's me but would you like to expand on "for obvious reasons". Given the lack of queries around setting up permissions for SQL Monitor I can only think that most?/many? sites are running it with the account in the sysadmin role which suggests to me that they don't much care what it does as long as it works?

Surely it would be better to educate DBAs into having properly locked down schema objects, in their own databases if appropriate, than to misuse(?) existing schema objects and thereby introduce workarounds to security best practice?

Possibly this is out of scope of this thread now, or I'm talking a load of rubbish as I am reasonably new to a role that requires me to secure SQL Server, but I'd be interested to get your thoughts on this.

Regards,
Paul

Russell D

Well - again at a basic level (and apologies for that but I'm not a DBA) - by obvious reasons I meant that it's quite a security hole to allow an external program the permission to create a database on your instances.

You are probably right though in that a lot of people have the monitor account running as SA, though I've no actual way of proving that.

I think it's probably more useful to you if I ask one of the [proper] DBAs to comment on this thread - it's a little beyond my scope.

pholden

Thanks Russell, I understand, would be great to get the opinion of one of your DBAs.