Competition: What’s your favorite Redgate tool? Enter now.

Permission for SQL monitor

xexexxexex Posts: 56
edited August 29, 2013 9:58PM in SQL Monitor Previous Versions
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 I avoid it?

Comments

  • 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
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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.
  • Yes, performance counters should work without ddl_admin.

    Thanks,
    Priya
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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.
  • priyasinhapriyasinha Posts: 548 Silver 1
    edited August 30, 2013 5:30AM
    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
    Priya Sinha
    Project Manager
    Red Gate Software
  • 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.
Sign In or Register to comment.