Does my Azure SQL Managed Instance has a read-only replica

Here are two SQL scripts you can use to check if your Azure SQL Managed Instance has a read-only replica:

1. Using sys.dm_fts_mirroring_endpoints:

FROM sys.dm_fts_mirroring_endpoints
WHERE mirror_state_desc = 'Synchronizing'
AND is_local = 0;

This script queries the sys.dm_fts_mirroring_endpoints dynamic management view, which contains information about mirroring and read-only replica endpoints. It filters for endpoints that are currently in the “Synchronizing” state and are not local (indicating a remote replica). If any results are returned, then your Managed Instance has at least one read-only replica.

2. Using sys.databases:

SELECT name, is_read_only_replica
FROM sys.databases
WHERE is_read_only_replica = 1;

This script queries the sys.databases system catalog view and filters for databases where the is_read_only_replica column is set to 1. This will directly show you the names of any read-only replica databases on your Managed Instance.

Both scripts will provide you with information about your read-only replicas, if any. The first script gives more details about the endpoint configuration, while the second script simply shows the names of the replica databases. Choose whichever script best suits your needs.

Additional notes:

  • These scripts only work on Azure SQL Managed Instances. They will not work on on-premises SQL Servers.
  • You need to have the necessary permissions to execute these scripts. The minimum required permission is the VIEW SERVER STATE server-level permission.
  • The first script might display results even if you have a secondary replica in a failover group, not a read-only replica. To distinguish between the two, you can check the endpoint_type column in the returned result set. A value of ‘2’ indicates a read-only replica.

I hope this helps!

Azure SQL Managed Instance Read-Only Replica

Feeling the heat of heavy read loads on your Azure SQL Managed Instance? Introducing the read-only replica, your knight in shining armor for scaling read performance and offloading pressure from your primary database. Buckle up, as we explore the magic of this powerful tool!

What is a Read-Only Replica?

The read-only replica is a near-exact copy of your primary database, maintained in real-time through continuous synchronization. Its sole purpose? Serving up blazing-fast read queries without impacting write operations on the primary. Think of it as a dedicated reading room for your busy database, allowing your primary instance to focus on write tasks without getting bogged down by read requests.

Benefits of Read-Only Replicas:

  • Enhanced Scalability: Handle more read traffic with ease. Add additional replicas like building blocks to evenly distribute read workload and meet ever-growing demands.
  • Improved Performance: Enjoy significantly reduced response times for read-heavy applications like reporting, analytics, and dashboards. Queries fly without impacting write performance on the primary instance.
  • Increased High Availability: If your primary goes down, a designated replica can seamlessly take over, minimizing downtime and ensuring data availability.
  • Cost Efficiency: Pay only for the replica’s storage and compute resources, not for additional read transactions on the primary. Optimize costs while scaling performance.

Who Needs Read-Only Replicas?

  • Applications with high read-to-write ratios: Reporting, analytics, data warehouses, dashboards, and web applications with heavy read traffic.
  • Businesses experiencing scaling challenges: Struggling to meet read demand with your current configuration? Replicas help you scale efficiently and keep up.
  • Organizations demanding high availability: Eliminate single points of failure and ensure continuous access to data even during primary instance outages.

Getting Started with Read-Only Replicas:

Setting up your read-only replicas is effortless. Azure SQL Managed Instance offers built-in replicas in the business-critical tier, accessible through a simple ApplicationIntent=ReadOnly flag in your connection string. And if you need even more flexibility, you can configure additional replicas with granular control over location and storage options.

Remember: Replicas are for reading only! Any write attempts will be redirected to the primary instance. However, you can leverage tools like database triggers to replicate data modifications back to the primary, keeping both instances in sync.

Unleash the Power of Read-Only Replicas:

By embracing read-only replicas, you’ll experience a revolution in your Azure SQL Managed Instance performance. Faster reads, smoother scaling, and enhanced high availability – all while optimizing costs. So, ditch the bottleneck and empower your database with the agility and resilience of read-only replicas. Your applications and users will thank you!

Ready to dive deeper? Check out these resources for more information:

Don’t let heavy read loads hold you back. Unleash the full potential of your Azure SQL Managed Instance with the power of read-only replicas!

Synchronous vs. Asynchronous: Demystifying Always On Availability Group Modes

Always On Availability Groups in SQL Server offer high availability and disaster recovery solutions. But under the hood, two distinct data synchronization methods dictate how changes flow between the primary and secondary replicas: synchronous and asynchronous. Choosing the right one hinges on your specific needs and performance priorities.

Synchronous Replicas: Dancing To the Same Beat

Imagine a synchronized dance troupe. Synchronous replicas mirror the primary replica’s actions step-by-step. Every transaction committed on the primary waits for confirmation from a quorum of synchronous replicas before finalizing. This ensures data consistency across all replicas. However, this wait introduces latency, potentially impacting user experience, especially on geographically dispersed replicas.


  • Guaranteed data consistency: No risk of data divergence between replicas.
  • Fast failover: Minimal data loss on failover due to close synchronization.
  • Read-scale availability: Secondary replicas can serve read-only workloads, reducing pressure on the primary.


  • Higher latency: Waiting for synchronous commits can impact performance.
  • Limited scalability: Large numbers of synchronous replicas can burden the primary.
  • Single point of failure: If the primary or quorum replica fails, transactions stall.

Asynchronous Replicas: Flying Solo with Lag

Asynchronous replicas, like solo dancers, apply changes independently. Transactions committed on the primary are queued and sent asynchronously to the secondary. This eliminates waiting, improving performance and scalability. However, a data lag can exist between replicas, creating potential inconsistencies.


  • Lower latency: Transactions commit faster without synchronous waits.
  • Higher scalability: Can handle more secondary replicas without impacting performance.
  • Resilience to failures: Asynchronous replicas continue receiving data even if the primary fails.


  • Potential data inconsistencies: Data lag can occur, leading to temporary data differences.
  • Slower failover: More data might need to be replayed on failover, increasing downtime.
  • Limited read-scale availability: Asynchronous replicas might not be fully synchronized for read workloads.

Choosing the Right Rhythm:

The choice between synchronous and asynchronous boils down to your tolerance for latency vs. data consistency:

  • Synchronous: Choose for mission-critical applications requiring constant data consistency and fast failover, even at the cost of performance.
  • Asynchronous: Choose for performance-sensitive applications where read-scale availability and scalability are crucial, despite a potential data lag.

A Final Note:

Always On Availability Groups offer flexibility with hybrid configurations. You can mix synchronous and asynchronous replicas within the same group based on specific needs. Remember to carefully evaluate your workload characteristics and desired recovery times before settling on a rhythm for your availability group.

Stay tuned for further articles delving deeper into specific aspects of Always On Availability Groups!

Check when was last updatestats was done by object name

I was told to find out the last time an update stats was done on an object.

I used this query to show me StatisticsUpdateDate which is the last date/time updatestats was run on the object

--UpdateStats last update
    OBJECT_NAME(object_id) AS [ObjectName],
    [name] AS [StatisticName],
    [stats_id]) AS [StatisticUpdateDate]

Hope this helps.

SQL Server Replication Explained

SQL Server Replication: Keeping Your Data in Sync

Imagine having multiple databases scattered across your organization, each containing crucial information but living in blissful isolation. What if changes made in one database need to be reflected in the others, ensuring everyone operates with the same up-to-date data? Enter SQL Server replication, a powerful tool that bridges the gap between isolated databases, keeping them synchronized and consistent.

Think of replication as a synchronized dance between databases. One database, the publisher, takes the lead, making changes to its data. The distributor, acting as the choreographer, receives these changes and distributes them to the subscribers, the eager followers who update their own data accordingly. This constant communication ensures everyone is on the same page, avoiding data inconsistencies and outdated information.

Why Use SQL Server Replication?

The benefits of keeping your databases in sync are numerous:

  • Enhanced Data Availability: Replication ensures geographically dispersed users access the latest data, regardless of their location.
  • Improved Disaster Recovery: In case of server failure, subscribers continue functioning with replicated data, minimizing downtime.
  • Scalability and Performance: Offload read-intensive workloads to subscribers, reducing pressure on the publisher and improving overall performance.
  • Data Sharing and Collaboration: Facilitate collaboration between departments by keeping relevant data synchronized across different databases.

Types of Replication in SQL Server

SQL Server offers different types of replication to cater to diverse needs:

  • Transactional Replication: Continuously copies changes from the publisher to subscribers, ensuring near real-time data synchronization.
  • Merge Replication: Allows for bi-directional data exchange, enabling updates made on subscribers to be propagated back to the publisher.
  • Snapshot Replication: Provides an initial copy of the publisher’s data to subscribers, followed by periodic updates to keep them in sync.

Setting Up Replication:

Implementing replication involves configuring the publisher, distributor, and subscribers, defining which data to replicate, and scheduling synchronization intervals. While it may seem complex initially, SQL Server provides intuitive tools and wizards to guide you through the process.

Beyond the Basics:

SQL Server replication goes beyond simple data synchronization. Advanced features like:

  • Filtering and transformation: Tailor what data gets replicated and how it’s transformed during the process.
  • Conflict resolution: Define how conflicting changes from different subscribers are handled.
  • Security: Implement robust security measures to protect replicated data from unauthorized access.

Unlocking the Power of Synchronization:

SQL Server replication is a valuable tool for organizations with geographically distributed databases or those requiring enhanced data availability and disaster recovery capabilities. By understanding its different types, functionalities, and configuration options, you can leverage its power to keep your data synchronized, consistent, and accessible, ultimately empowering informed decision-making across your organization.

So, the next time you find yourself juggling multiple databases, remember SQL Server replication – the conductor of your data symphony, ensuring everyone plays in perfect harmony.

I hope this article has provided a comprehensive overview of SQL Server replication.

What is SQL Server Query Store

SQL Server Query Store: A Deep Dive into Performance Insights

Imagine peering into the inner workings of your SQL Server, witnessing the intricate dance of queries and their performance in real-time. That’s the power of the SQL Server Query Store, a built-in tool that sheds light on your database’s health and efficiency.

Unveiling the Mystery of Query Execution

Gone are the days of blind troubleshooting, chasing slow queries like phantoms in the SQL Server labyrinth. The Query Store acts as a performance detective, capturing a wealth of data about every query executed:

  • Query text and plan: See exactly what queries are running and how they’re planned for execution.
  • Runtime statistics: Track execution time, CPU usage, I/O operations, and other metrics to pinpoint bottlenecks.
  • Wait statistics: Identify locks, blocking sessions, and other resource contentions impacting performance.
  • Historical trends: Analyze query performance over time and spot anomalies or regressions.

With this treasure trove of information at your fingertips, you can:

  • Identify and optimize slow queries: Quickly pinpoint the culprits behind sluggish performance and tune them for efficiency.
  • Prevent performance regressions: Track changes in query plans and performance metrics to proactively address potential issues.
  • Understand workload patterns: Gain insights into how your database is used at different times, enabling better resource allocation.
  • Troubleshoot query-related issues: Diagnose specific problems like locking or blocking with detailed execution data.

Beyond Basic Monitoring: A Toolbox for Optimization

The Query Store isn’t just a passive observer; it’s an active participant in your performance optimization journey. Here’s how:

  • Query plan forcing: Fix inefficient query plans by forcing a specific plan to be used, ensuring consistent performance.
  • Query history cleanup: Manage storage space by defining policies for automatically archiving or deleting historical data.
  • Alerts and notifications: Set up alerts for specific performance thresholds, like slow query execution or resource exhaustion, to be notified proactively.

Unlocking the Potential of Your SQL Server

The SQL Server Query Store is a powerful tool, but its effectiveness hinges on proper utilization. Here are some tips to make the most of it:

  • Enable Query Store on all databases: Don’t miss out on valuable insights; activate it across your entire SQL Server environment.
  • Define capture policy: Tailor data collection based on your needs, balancing performance impact with data granularity.
  • Leverage built-in analysis tools: SQL Server Management Studio offers intuitive dashboards and reports to visualize and analyze query store data.
  • Integrate with third-party tools: Extend your analysis capabilities with specialized tools for deeper performance insights and optimization recommendations.

Empowering Informed Decisions with Data-Driven Insights

The SQL Server Query Store empowers you to move beyond guesswork and intuition in managing your database. By harnessing its data-driven insights, you can optimize performance, prevent issues, and ensure your SQL Server runs like a well-oiled machine. So, dive into the Query Store, unveil the secrets of your queries, and unlock the full potential of your SQL Server environment.

Remember, the Query Store is just one piece of the puzzle. Combining it with other performance monitoring tools and best practices can create a comprehensive strategy for maintaining a healthy and efficient SQL Server infrastructure.

We can enable Query Store thru the SSMS GUI or by SQL Script.
Here is the SQL Script to turn on Query Store for a database:


SQL Server adds default values for Query Store parameters. In SQL Server 2019 Microsoft decided to change default values for some of them. Here is the table with the attributes and former and current default values:

AttributeSQL Server 2017SQL Server 2019

I hope this article has given you a deeper understanding of the SQL Server Query Store and its potential to revolutionize your database performance management.

Create SQL Script to update stats for all tables in a database with Full Scan

I was asked to run update stats on all the tables in a database with full scan.
And I was to use native SQL code not OLA or another open source.
Additional requirement was to update stats on each table individually listed as a separate line of code.

The database had multiple tables so to list each individual items was going to very burdensome.

Thankfully I found this sql script that will generate an update stat statement for each table in the database. You just have to copy the query output to either a new query window or just put it in a sql job and run it.

SELECT table_schema, table_name
FROM information_schema.tables
OPEN updatestats
DECLARE @tableSchema NVARCHAR(128)
DECLARE @tableName NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)
FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
  SET @Statement = 'UPDATE STATISTICS '  + '[' + @tableSchema + ']' + '.' + '[' + @tableName + ']' + ' WITH FULLSCAN'
  PRINT @Statement -- comment this line out if you want to actually run the query instead of just getting the code for the task.
 --EXEC sp_executesql @Statement -- remove the comment to run the command 
  FETCH NEXT FROM updatestats INTO @tableSchema, @tableName
CLOSE updatestats
DEALLOCATE updatestats

Hope this helps, enjoy!

Backup database to a static file and overwrite the backup file every time the backup job runs

I had a request from a business user to create a job that they want to run on an ad-hoc basis to backup a database to a static file name. And they backup file needed to be deleted/overwritten every time the sql job ran. I used Ola Hallengren’s backup code to take the database backup with a static file name.

EXECUTE [dbo].[DatabaseBackup] 
@Databases = 'DatabaseName', 
@Directory = N'E:\DeployBackups', 
@BackupType = 'FULL', 
@Verify = 'Y', 
@CheckSum = 'Y',
@Compress = 'Y',
@CleanupMode = 'BEFORE_BACKUP',
@Init = 'Y',
@FileName = '{DatabaseName}.{FileExtension}'

@Databases = specify the name of the database
@Directory = the directory where the backup file will be written to
@BackupType = what type of backup – FULL, DIFF or LOG
@Verify = verify the backup file after it is completed
@CheckSum = check the checksum of the database file
@Compress = compress the backup file
@CleanupMode = when should the old backup file will be deleted – before or after the current backup job is completed. If you have limited space on the drive then it is a good option to delete the old backup file before the new backup is started but you run the risk of losing all the backup files incase the current backup job fails.
@Init = ‘Y’, or ‘N’ specifies if the current backup files will be deleted. It is important to note that we need to use this option since we are backing up the database to a static file name. If you do not use this option then the backup will be appended with the new backup and the size of the backup file will be growing.
@FileName = ‘{DatabaseName}.{FileExtension}’ this option specifies the exact name of the database file every time the backup runs.

Hope this helps, good luck.

Search all SQL objects for a specific text

I was tasked to replace all the db mail profile name to a new profile name in all the objects in a database that uses specific old profile name. This can easily turn into a tedious task to manually open/edit all the sql server jobjects in each database.

I wrote this query to quickly find all the sql objects that use the specific db mail profile name. This will give you are list of all the sql objects in the Results window. Now you simply have to go by this list to edit/update each of these specific stored procs with the new db mail profile name.

WHERE [text] LIKE '%@profile_name%'  
--AND OBJECTPROPERTY(id, 'IsProcedure') = 1  

Hope this helps.

What is SQL Server Availability Groups

SQL Server Availability Groups are a high availability and disaster recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, Availability Groups maximize the availability of a set of user databases for an enterprise. An Availability Group supports a failover environment for a set of user databases, known as availability group or AG databases, that fail over together. An Availability Group supports a set of read-write primary databases and one to eight sets of corresponding secondary databases. The number of supported databases varies by which edition of SQL Server you are running – Standard or Enterprise Edition.

High availability Availability Groups can provide automatic failover to a secondary replica if the primary replica fails. This helps to ensure that applications can continue to access databases even if there is a hardware failure or other unplanned outage. This can be used to replicate databases to a remote site, providing protection against site-wide disasters. It can also be used to distribute read-only workloads to secondary replicas, which can help to improve performance and scalability for applications.

Availability Groups can be configured with synchronous or asynchronous replication. So, what is the difference?

Synchronous replication: In synchronous replication, the primary replica waits for an acknowledgment from all secondary replicas before committing a transaction. This ensures that all replicas are always in sync, but it can also impact performance, especially if the secondary replicas are located far away from the primary replica.

Asynchronous replication: In asynchronous replication, the primary replica does not wait for an acknowledgment from the secondary replicas before committing a transaction. This allows for higher performance, but it also means that the secondary replicas may not be completely up-to-date with the primary replica.

When to use synchronous replication:

  • When you need the highest possible data consistency
  • When you can tolerate some performance impact
  • When your secondary replicas are located close to the primary replica

When to use asynchronous replication:

  • When you need the highest possible performance
  • When you can tolerate some data loss
  • When your secondary replicas are located far away from the primary replica
  • When you are using Always On availability groups for disaster recovery

In general, synchronous replication is recommended for production environments where data consistency is a top priority. Asynchronous replication is recommended for development and test environments, and for production environments where performance is a top priority and data loss can be tolerated.

To create an Always On Availability Group, you must first create a Windows Server Failover Cluster (WSFC). Once you have created a WSFC, you can use the CREATE AVAILABILITY GROUP using Transact-SQL (T-SQL) statement to create the AG or thru the SSMS GUI. The CREATE AVAILABILITY GROUP statement specifies the name of the AG, the databases to include in the AG, and the replicas for the AG.

Hope this gives you some important information on SQL Server Availability Groups.

Grant access to Azure Synapse

Create the user in the master database

--Use Master Database
ALTER ROLE dbmanager ADD MEMBER [] ;
ALTER ROLE loginmanager ADD MEMBER [];
Grant Alter any user to [] with GRANT OPTION

Now select the user database from the db drop down menu in SQL Server Management Studio

--Use UserDatabase
ALTER ROLE db_owner ADD MEMBER []; --this grants the db permission to the user
Grant Alter any user to [] with GRANT OPTION  --this grants the user permission to modify permissions for other users.
EXEC sp_addrolemember '[schema_name]','' --this adds the user to a specify schema role

Hope this helps you to grant users permission to Azure Synapse Databases.

Database Mail Troubleshooting

Database Mail Troubleshooting

I use the following scritps to troubleshoot database mail issues.

–Get a list of email sent items
SELECT * FROM dbo.sysmail_sentitems

–Another version of the script to get a list of email sent items
USE msdb
SELECT sent_status, *
FROM sysmail_allitems
order by send_request_date desc

–Check to see if broker service is enabled
SELECT is_broker_enabled FROM sys.databases WHERE name = ‘msdb’;

–Check if database mail service is running
EXECUTE msdb.dbo.sysmail_help_status_sp

For SQL 2016 – you will need to have .net 3.5 installed on the server for db mail to work

EXEC msdb.dbo.sysmail_help_configure_sp;

–Get the name of the database profile and account name
EXEC msdb.dbo.sysmail_help_account_sp; –this will show the db mail account name
EXEC msdb.dbo.sysmail_help_profile_sp; –this will show the db mail profile name
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

Disable Foreign Keys

I received a request to export data from a table in a database in Production to a similar table in a database in the Development environment. I used the export/import wizard thru SQL Server Management Studio but my export was failing giving me an error that the data cannot be copied because Foreign Key was present in the destination database. In the past I would just script out a drop and create script for all the foreign keys then drop all the Foreign Keys, do the data export and then re-create the Foreign Keys. After some research online I came across a better option to just disable the Foreign Keys instead of dropping and recreating them.

But first lets understand what is a Primary Key and Foreign Key.

In SQL Server, a primary key is a single field that has a unique value to define a record. Fields that are part of the primary key cannot contain a null value. A table can have only one primary key. Usually the primary key is used as an index but this can vary.

A table can have only ONE primary key and this primary key can consist of single or multiple columns (fields).

Since primary key constraints ensure unique data, they are often called identity columns.

When you designate a primary key constraint for a table, the SQL engine enforces data uniqueness by auto create a unique index for the primary key columns.

A foreign key is a column or set of columns that allows developers to establish a referential link between the data in two different tables. This link helps to match the foreign key column data with the data of the referenced table data. The referenced table is called the parent table and the table that involves a foreign key is called the child table. In addition, if a foreign key references another column of the same table, this reference type is called a self-reference.

A FOREIGN KEY is a field (or collection of fields) in one table, that links to the PRIMARY KEY in another table.

The table with the foreign key is called the child table, and the table with the primary key is called the referenced table (parent table).

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column in the child table, because it has to be one of the values contained in the parent table.

Based on the developer’s coding standard – usually it is a good practice to prefix with FK_{FK name} and the same goes with Primary Keys being prefixed with PK_{PK Name}

The following SQL query creates a FOREIGN KEY on the “PersonID” column in the Persons table when the “Orders” table is created:


OrderID int NOT NULL,

OrderNumber int NOT NULL,

PersonID int,




If the Orders table is already created then use this SQL query to create a FOREIGN KEY constraint on the “PersonID” column:



If you need to name a Foreign Key constraint and to specify a Foreign Key constraint on multiple columns, use this SQL Query:




You can disable a Foreign Key in a table using the Alter Table statement in SQL Server Management Studio. Here is the syntax to disable a foreign key in SQL Server (T-SQL):

ALTER TABLE [your_table_name]

NOCHECK CONSTRAINT [your_fk_name];



The name of the table where the foreign key has been created.


The name of the foreign key that you wish to disable.

The above script would use the ALTER TABLE statement to disable the constraint called fk_inyour_fk_name on the your_table_name table.

After you have disabled the Foreign Key then you should be able to do your data load using the Daa with no error.

To disable all constraints

— disable all constraints


To turn the constraints back on – the print command is optional and it is just for listing the database tables.

Run this:

— enable all constraints

exec sp_MSforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

To disable the constraints is much helpful when you have to copy data from one database to another. I prefer this then dropping constraints.

If you have triggers in the database then you will have to disable the triggers prior to your data load and then add the triggers back on once the data load is completed.

To disable all constraints and triggers run this:

sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all constraints and triggers run this:

exec sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”

sp_msforeachtable @command1=”print ‘?'”, @command2=”ALTER TABLE ? ENABLE TRIGGER all”

The word of caution is disabling constraints and triggers – to you have make sure there are no new deltas being written to the database by the users because once you disable all the constraints and triggers any new deltas written to the database that might violate the integrity of the database. Hence you have to ensure that all application traffic is stopped.

Also if you need to import a large amount of data, then consider using BULK INSERT because this method does not fire the triggers. However after your bulk insert it completed, you will need to fix any data integrity issues which occurred during your bulk insert that circumvented the trigger policies.

Hope this helps clarify the concept of Primary Keys, Foreign Keys and Constraints.

Grant Users permission to edit SQL Job Schedules

If a non-sysadmin users requests permission to modify sql job schedules that are not the owner of then you can do the following:

Grant the user execute permission to


Here is the TSQL for it:

GRANT EXECUTE ON sp_update_job to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]
GRANT EXECUTE ON sp_update_jobstep to [username]

I also tried granting the user db_owner to the msdb database. But the user was still not able to edit the sql server job schedule.

The user kept getting this error:

On researching the error code, there seems to be no other option but to grant the user sysadmin permission to be able to edit the sql job schedule for all the jobs on the sql instance.

Only sysadmin role members can edit and run jobs owned by others.

Then I came across this info:

Grant execute permission to these stored procs

GRANT EXECUTE ON sp_update_job to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]
GRANT EXECUTE ON sp_update_jobstep to [username]

GRANT EXECUTE ON sp_add_job to [username]
GRANT EXECUTE ON sp_add_jobstep to [username]
GRANT EXECUTE ON sp_add_jobschedule to [username]
GRANT EXECUTE ON sp_update_job to [username]
GRANT EXECUTE ON sp_update_jobstep to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]

GRANT EXECUTE ON sp_help_job to [username]
GRANT EXECUTE ON sp_help_jobstep to [username]
GRANT EXECUTE ON sp_update_jobschedule to [username]

GRANT EXECUTE ON sp_delete_jobschedule to [username]
GRANT EXECUTE ON sp_help_jobhistory to [username]
GRANT EXECUTE ON sp_start_job to [username]
GRANT EXECUTE ON sp_stop_job to [username]

GRANT EXECUTE ON sp_delete_job to [username]
GRANT EXECUTE ON sp_delete_jobstep to [username]

And it worked!! so no need to grant the user sysadmin rights to the sql instance.


There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it There are occasions when database users will reach out to me get elevated database permissions to run DMVs. Members of sysadmin roles can view the results of Dynamic Management Objects but sometimes it is helpful to grant this permission to non-dba personnel if they need to do any performance troubleshooting.

Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose sql server problems, and tune databae performance.

There are two different types of DMVs and functions:

Server-scoped DMVs and functions – These require VIEW SERVER STATE permission on the SQL Server level.
Database-scoped DMVs and functions – These require VIEW DATABASE STATE permission on each of the database.

VIEW SERVER STATE is a server level permission that grants non-sysadmin users the ability to view results of Dynamic Management Views.

Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a Beginning with SQL Server 2005 dynamic management objects are database views or functions that shows specific information or the state of the SQL Server instance for the overall SQL Server or for a given database instance at a given time. Since DMVs were introduces in SQL Server 2005 and with each new release of SQL Server, Microsoft has been adding additional DMVs (Dynamic Management Views) to help troubleshoot the performance of the SQL Servers. These objects are helpful to monitor the database server in an efficient and controlled manner.

DMVs come in two subsets – Dynamic Management Views (DMVs) and DMFs (Dynamic Management Functions) and are classified as Dynamic Management Objects (DMOs).
DMVs are like any other views where you can select data from them. DMVs require values to be passed to the function just like any other functions. I will go into the details of DMFs in a separate blog.

If the user does not have sysadmin priveleges or has been granted VIEW SERVER STATE permission and tries to run the following as an example:

SELECT * FROM sys.dm_os_wait_stats

The user will see an error like this one:
Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

We run the following query to grant the user the access to the entire SQL Server Instance:


This needs to be run on the master database.
Once the above script is executed successfully then that specific user will be able to view Dynamic Management Objects to do any type of performance troubleshooting.

In the case of a individual database scoped access, a user might have data reader access to the database but when the users runs this query:

SELECT * FROM sys.dm_db_partition_stats

They get an error, because this query is trying to access a DMV in the specific database.

WE need to grant VIEW DATABASE STATE for this.

We run the following query to grant the user the access to a specific database:

USE [DatabaseName]

Once you run the above script then the user will be able to get a result from:

SELECT * FROM sys.dm_db_partition_stats

Now lets talk about the security risks for granting VIEW SERVER STATE permission.
Yes, there is a risk in providing VIEW SERVER STATE to non-dba and non-sysadmin personnel since they aren’t supposed to see this level of intricate database information on the sql instance.
As an example: sys.dm_exec_connections, sys.dm_exec_cached_plans, sys.dm_exec_requests, sys.dm_exec_query_stats, all provide information about execution contexts and plans, and when the user uses these with sys.dm_exec_sql_text() or sys.dm_exec_query_plan(), it will give this user information about the sql code and objects in the databases. This is usually a security violation in some organizations.

The VIEW SERVER STATE permission gives the database user unrestricted access to this information when using these DMVs. Althought they cannot change anything in the database, but they can get detailed database execution info that may not be allowed to view based on the user’s organizational responsibilites. I would analyze this type of access request with a grain of salt and ask some questions as to the security implications of this user requesting access having access to the database execution info. Also the user will be able to use DMV’s to look at queries. If the queries or some query parameters can contain confidential information that the user wouldn’t otherwise be able to see prior to granting this access – allowing VIEW SERVER STATE would allow them to do so (dateofbirth or socialscecurity #s)

Hope this helps clarify the differences between VIEW SERVER STATE and VIEW DATABASE STATE permission grants.