Category: Uncategorized

Minimum necessary permissions to monitor SQL server instance – Red Gate SQLMonitor

  1. The account used to monitor your SQL Server instances should have the following permissions:
  2. Member of the sysadmin role (role required for Integrity check overdue alerts (to run DBCC DBINFO) and to allow SQL Monitor to tur on the deadlock trace flag.
  3. If you are unable to grant sysadmin permissions to the account. Grant the following permissions:
  4. Member of the db_datareader role on the msdb system database.
  5. Member of the SQL_AgentReader role on the msdb system database.
  6. Member of the db_ddladmin database role on all databases (needed to run sys.dm_db_index_physical_stats() required by the Fragmented index alert).
  7. VIEW ANY DEFINITION server permission.
  8. ALTER TRACE server permissions (if you want to enable trace data).
  9. VIEW SERVER STATE and VIEW DATABASE STATE database permissions on all databases.
  10. Member of the db_owner role on the tempdb database.
  11. EXECUTE on xp_readerrorlog.
  12. Below is the script to grant the non-sysadmin permissions described above:
USE [msdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of the db_datareader role on the msdb system database*/
ALTER ROLE [db_datareader] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [msdb]
GO
/*Member of SQLAgentReader role on the msdb system database*/
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
CREATE USER [Domain\SQLServerAccount] FOR LOGIN [Domain\SQLServerAccount]
GO
USE [tempdb]
GO
/*Member of the db_owner  role on the tempdb database*/
ALTER ROLE [db_owner] ADD MEMBER [Domain\SQLServerAccount]
GO
use [master]
GO
/*ALTER TRACE server permission*/
GRANT ALTER TRACE TO [Domain\SQLServerAccount]
GO
use [master]
GO
/*VIEW ANY DEFINITION server permission*/
GRANT VIEW ANY DEFINITION TO [Domain\SQLServerAccount]
GO
USE master;
GRANT EXEC ON xp_readerrorlog TO [Domain\SQLServerAccount];


/* Run the output of the below script in a separate window in order to grant db_ddladmin role and view database state permission to the user*/
USE [master]
GO
DECLARE @UserName VARCHAR(25) = 'Domain\SQLServerAccount'
SELECT 'USE ['+name+'] CREATE USER ['+@UserName+'] FOR LOGIN ['+@UserName+']; ALTER ROLE db_ddladmin ADD MEMBER ['+@UserName+']; GRANT VIEW DATABASE STATE TO ['+@UserName+'];'
FROM sys.databases

Frequently Asked Questions

Q: Why is the same query sometimes fast and sometimes slow, depending on where I run it?

It’s most likely parameter sniffing.

Q: What should you do when your index maintenance jobs take forever?

When you rebuild a 50GB table’s indexes, you’re basically reloading that table from scratch. SQL server has to make a brand-new copy of the table on new data pages, and it logs all this stuff in the transaction log – which means your backups take longer and your Availability Group gets way far behind.

If you’ve been rebuilding indexes nightly, consider easing that off to weekends instead. If you’re worried that will affect performance, you’re probably mixing up the difference between rebuilding indexes and updating statistics. Consider doing daily stats update jobs rather than rebuilding your indexes.

In general, statistics updates are way better than daily index rebuilds.

Q: Which cloud provider should you use to host SQL server?

  • If you run SQL Server in a VM, you can get m ore VM selection & performance at AWS
  • If you want to rent SQL server as a service, Microsoft’s Azure SQL DB Managed instances are quite a bit ahead of Amazon RDS