Updated from 3.x to 4 - insufficient credentials error now

joshuawferguson

Since updating to SQL Monitor 4 I'm having trouble getting monitoring working again. As far as I can tell using Redgate's official documentation, I have the SQL account permissions set correctly, yet the log shows this:

The INSERT permission was denied on the object 'redgate_sqlmonitor_topqueries_b_lftcadsrv.(local)_redgate-sql', database 'tempdb', schema 'dbo'.

I added db_datareader and db_datawriter permissions to the account, and monitoring resumed fine. Is there something I'm missing that may have caused this error? Thanks.

Brian Donahue

Hello,

I've never seen SQL Monitor 3.x try to write to tempdb, so it must be part of the new performance analysis in v4. Most users would have the ability to write to tempdb as it's used implicitly by SQL Server as storage space for joins and other things. Thanks for pointing that out.

joshuawferguson

Thanks for the reply - are these permissions (db_datareader/writer) going to be reflected in the official documentation?

Brian Donahue

I've requested that this is included in future.

joshuawferguson

I know this is a bit after the fact, but I found (on another server) that if sqlmonitor's login doesn't have ddl_admin rights on tempdb, it also fails. By default (without this ddl_admin right) the login should be capable of creating temporary tables (with the # prefix) in tempdb. Why is sqlmonitor trying to create permanent tables in tempdb? Seems very counter intuitive. . .

thtwill

Thanks for this topic, it helped me too. I encountered this same issue on a fresh install of version 4.
I agree, this was not mentioned in the official documentation page (http://documentation.red-gate.com/display/SM4/Requirements) at time of this post.

TheLonelyDBA

the same happens on the hosted beta of SQL Monitor: it tries to create "permanent" tables in TempDB.

Alex B

Hello all,

I know it's quite a bit after the fact, but for anyone reading I wanted to mention the documentation has now been updated to include the need for db_datareader and db_datawriter on tempdb.

Kind regards,
Alex