Permission for SQL monitor

xexex

ref to http://www.red-gate.com/supportcenter/c ... ermissions

SQL Monitor need a account with ddl_admin. However, ddl_admin can drop any tables and databases, how can Ｉ avoid it?

priyasinha

Hi,

SQL Monitor runs DBCC SHOWCONTIG for collecting index information. SQL Server only allows users with db_ddladmin permission to run this command.

You can use an account without this permission but in that case you wont get any 'Fragmented index' alert.

Thanks,
Priya

xexex

Other then "Fragmented index", does the performance counters such as processor usage, SQL lock wait time, database active transaction work as well without assigning ddl_admin?

Thanks for your reply.

priyasinha

Yes, performance counters should work without ddl_admin.

Thanks,
Priya

xexex

I've removed build-in\administrators on our SQL 2000, add SQL Monitor service account to MASTER - datareader, MSDB - datareader, but I receive this error:

Monitoring stopped (Incorrect credentials or insufficient permissions)

Server User 'domain\sqlmonitor' is not database 'model' valid user.
Server User 'domain\sqlmonitor' is not database 'model' valid user.
EXECUTE user permission on object 'xp_sqlagent_enum_jobs',database 'master'，owner 'dbo' rejected.
database 'tempdb' ，FN_TRACE_GETINFO permission rejected.
P.S.% The error message are translated from Chinese, sorry about that.

priyasinha

Hi,

You are getting first two errors because you have not created/ mapped a database user to this login.

But I have tried this now on SQL 2000 and it looks like if the account doesn't have permission on FN_TRACE_GETINFO this then it doesn't work. I would have expected it to throw an error but still work which is not the case. I have raised an issue now and tracking number is SRP-9189. Unfortunately, at the moment you would have to give sa permission. We will get this issue fixed for next release.

Thanks,
Priya

xexex

I've already granted datareader to this account on "model, "master", "msdb" and "tempdb", but it still show the error.

I need to grant db_owner on "master" to solve this problem. However, the error of FN_TRACE_GETINFO still be obtained.

I'm waiting for the next release.

Thanks for help.