The EXECUTE permission was denied on the object 'sp_help'

Question

 

I get the following error when trying to execute sp_help as a user who's only access is to the db_datareader role of a user database.

 

Msg 229, Level 14, State 5, Procedure sp_help, Line 1

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

 

Attempting the same thing on a similar SQL Server using same security level is successful.

 

Both are build 9.00.3152.00.

 

 

Any ideas?

Answer 1

The only idea that I have is that either they are not both the same version of SQL Server, (OR), the security settings are not the same.

 

But since you have eliminated those options, I don't know where to go with this...

 

Answer 2

Can you check the public permissions for sp_help in the master db? They may have an explicit deny on them...

 

HTH!

Answer 3

Thanks! I was finally able overcome this.  It seems permissions were restricted on the file system for the account used to apply SP2.  The permissions were addressed and SP2 was reapplied.

 

I believe this issue resulted from the issue above.

 

To resolve I executed

 

 

Use Master

Go

GRANT EXECUTE ON sp_help to Public

 

There were actually a handful of sp's I had to do this do.