Red Gate forums :: View topic - Permission for SQL monitor
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Permission for SQL monitor

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
xexex



Joined: 24 Jun 2010
Posts: 41

PostPosted: Fri Aug 23, 2013 3:31 am    Post subject: Permission for SQL monitor Reply with quote

ref to http://www.red-gate.com/supportcenter/content/SQL_Monitor/help/3.2/SM_Account_Permissions

SQL Monitor need a account with ddl_admin. However, ddl_admin can drop any tables and databases, how can I avoid it?
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Fri Aug 23, 2013 9:17 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
xexex



Joined: 24 Jun 2010
Posts: 41

PostPosted: Tue Aug 27, 2013 7:43 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Tue Aug 27, 2013 9:52 am    Post subject: Reply with quote

Yes, performance counters should work without ddl_admin.

Thanks,
Priya
Back to top
View user's profile Send private message
xexex



Joined: 24 Jun 2010
Posts: 41

PostPosted: Thu Aug 29, 2013 9:44 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
priyasinha



Joined: 03 Jan 2007
Posts: 530

PostPosted: Thu Aug 29, 2013 1:08 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
xexex



Joined: 24 Jun 2010
Posts: 41

PostPosted: Fri Aug 30, 2013 2:58 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group