SSMS – AWS/RDS – Error – “The EXECUTE permission was denied on the object ‘xp_regread'”

Background

When SQL Server Management Studio ( SSMS ) is ran against an Amazon/RDS SQL Server instance, we can get a little error.

Error

The error reads :-

Error Message

Image

EXECUTEPermissionWasDeniedOnTheObject.xp_regread.01.20200323.0623PM.PNG

Text


The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'.

The EXECUTE permission was denied on the object 'xp_regread', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

Error Message – Advanced Information

Image

EXECUTEPermissionWasDeniedOnTheObject.xp_regread.AdvancedInformation.01.20200323.0626PM.PNG

Text


 Microsoft.SqlServer.Management.Common.ServerConnection.GetExecuteReader(SqlCommand command)
   at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataReader(String query, SqlCommand& command)
   at Microsoft.SqlServer.Management.Smo.DataProvider.SetConnectionAndQuery(ExecuteSql execSql, String query)
   at Microsoft.SqlServer.Management.Smo.ExecuteSql.GetDataProvider(StringCollection query, Object con, StatementBuilder sb, RetriveMode rm)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
   at Microsoft.SqlServer.Management.Smo.SqlObjectBase.GetData(EnumResult erParent)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
   at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
   at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
   at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.Agent.JobServer.ImplInitialize(String[] fields, OrderBy[] orderby)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
   at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetValueWithNullReplacement(String propertyName, Boolean throwOnNullValue, Boolean useDefaultOnMissingValue)
   at Microsoft.SqlServer.Management.Smo.Agent.JobServer.get_MsxServerName()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.AgentPropertyHandler.SetTsxInformation(Server server, INameObjectCollection properties)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.AgentPropertyHandler.PopulateProperties(INodeInformation source, INameObjectCollection properties)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NodeContext.PopulateProperties()
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.SetStaticProperties(NodeContext nodeContext)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItemWithQuery(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, String urnQuery, Boolean registerBuilder, Boolean registerBuiltItems)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.BuildDynamicItem(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItemBuilder.Build(IList`1 nodes, INodeInformation source, INavigableItem sourceItem, IFilterProvider filter)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.NavigableItem.RequestChildren(IGetChildrenRequest request)
   at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ExplorerHierarchyNode.BuildChildren(WaitHandle quitEvent)

Explanation

Looking at the Stack Trace the error occurs at :-

Microsoft.SqlServer.Management.Smo.Agent.JobServer.get_MsxServerName()

TroubleShooting

What is the MsxServer ?

Definition

Docs / Previous Versions / SQL / SQL Server 2008 / Properties (SQL-DMO) / M (SQL-DMO Properties) / MSXServerName Property
Link

The MSXServerName property identifies the master server for an enlisted target server.

Explanation

SQL Server Agent can be configured to manage other servers. When configured to do so the servers managed are considered enlisted.

 

Remediation

Microsoft

Microsoft itself states :-

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Administrator

The error only occurs for principals/logins/accounts that have access to the msdb database.

The error occurs when SSMS connects to the msdb database and attempts to read SQL Server Metadata information.

Version

Tested against SQL Server Management Studio ( SSMS ) :-

  1. v18.x
  2. v17.x

 

Azure Feedback

  1. SSMS – Error – “The EXECUTE permission was denied on the object ‘xp_regread'”
    Opened by : Daniel Adeniji
    Dated :- 2020-March-29th
    Link

 

Reference

  1. Microsoft
    • SQL-DMO
      • Docs / Previous Versions / SQL / SQL Server 2008 / Properties (SQL-DMO) / M (SQL-DMO Properties) / MSXServerName Property
        Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s