Permission for SQL monitor

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Permission for SQL monitor

Postby xexex » Fri Aug 23, 2013 3:31 am

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?
xexex
 
Posts: 41
Joined: Thu Jun 24, 2010 5:50 am

Postby priyasinha » Fri Aug 23, 2013 9:17 am

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
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Postby xexex » Tue Aug 27, 2013 7:43 am

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.
xexex
 
Posts: 41
Joined: Thu Jun 24, 2010 5:50 am

Postby priyasinha » Tue Aug 27, 2013 9:52 am

Yes, performance counters should work without ddl_admin.

Thanks,
Priya
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Postby xexex » Thu Aug 29, 2013 9:44 am

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.
xexex
 
Posts: 41
Joined: Thu Jun 24, 2010 5:50 am

Postby priyasinha » Thu Aug 29, 2013 1:08 pm

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
Last edited by priyasinha on Fri Aug 30, 2013 10:30 am, edited 1 time in total.
priyasinha
 
Posts: 532
Joined: Wed Jan 03, 2007 5:02 pm

Postby xexex » Fri Aug 30, 2013 2:58 am

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.
xexex
 
Posts: 41
Joined: Thu Jun 24, 2010 5:50 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests

cron