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

_{Friday, December 22, 2017 11:48 AM}

Hi All,

I have installed SQL Server 2017 version on the Windows 2012R2, it was installed successfully without any error/issues and I have created one test database and test SQL authentication, provided DB_Onwer access to the test Db.

When I am trying to access the SSSM, it throwing an error like ‘EXECUTE permission was denied on the object 'xp_instance_regread', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229) ‘.

We know it’s knew error with past SQL versions and we used to grant access (GRANT EXECUTE ON [sys].[xp_instance_regread] TO [public]) , It’s resolving the issues.

Why we need to grant access to public every time, is there any permanent solution from MS without granting the access with the new SQL build??

Thanks

Rahul Kattuguri

_{Friday, December 22, 2017 12:03 PM}

which is the SSMS version you installed?

what were you trying to do in SSMS when you got this error?

Please Mark This As Answer if it solved your issue
Please Vote This As Helpful if it helps to solve your issue
Visakh

My Wiki User Page
My MSDN Page
My Personal Blog
My Facebook Page

_{Friday, December 22, 2017 1:56 PM}

I think only members of Sysadmin fixed server role have permission to execute extended stored procedures by default, as a security precaution.

Extended Stored Procedures (xp_) are deprecated and will be removed in future versions of SQL Server.

HTH,

Phil Streiff, MCDBA, MCITP, MCSA

_{Friday, December 22, 2017 10:34 PM}

For what it's worth, on my SQL 2017 instance, public has permissions to xp_instance_regread. It sounds like someone has gone a lockdown craze on the instance you work. I believe the new Vulnerability Assessment that comes with SSMS 17.4 suggest that permissions on this xp should be revoked.