Follow

2A. SQL Server: Windows login

In the Windows service configuration application, you must setup a SQL User on the Microsoft SQL servers that you want to monitor.

It is your responsibility to make sure that this user can't do any damage on the system, and we strongly recommend that you DON'T use sysadmin server role.

Instead we suggest that you grant the following rights to the user:

  1. VIEW SERVER STATE
  2. VIEW ANY DATABASE
  3. VIEW ANY DEFINITION
  4. Grant execute rights to xp_readerrorlog (see below)
  5. Grant Select to tables in MSDB (See below)

 

To grant execute on xp_readerrorlog do the following:

 use master
go
create user testlogin for login testlogin
go
grant exec on xp_readerrorlog to testlogin

 

To Grant select on tables in MSDB do the following

use msdb
go
create user testlogin for login testlogin
go
grant select on sysjobs to testlogin
grant select on sysjobhistory to testlogin

 

To create user in all userdatabases do the following

declare @command nvarchar(200)
declare @username    nvarchar(20)
set @username='testlogin'

set @command='    use [?]
                if not exists (select * from sys.database_principals where name='''+@username+''')--
                begin
                    create user '+@username+' for login '+@username+'
                end
                '
EXEC dbo.sp_MSForEachDB @command


 

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.