The EXECUTE permission was denied on the object TableExists

Question

Hi All,

SQL Server 2012 SP1 - Windows 2008 R2

I have a database user where I gave it db_datareader, db_datawriter, db_ddladmin and db_securityadmin permissions. When the user ran below quey, she got

The EXECUTE permission was denied on the object 'TableExists', database 'MyTESTDB', schema 'dbo'.    Error.

-- Query:

IF dbo.TableExists('sco_runtime_error_log') = 'T'
BEGIN
    DROP TABLE sco_runtime_error_log;
END
GO

Can anyone tell me why ?

Thanks.

Answer 1

Hi Carlos,

With the permissions that you mentioned above, the Drop table statement can be executed directly. But to execute the entire set of above mentioned code you need to provide execute permission to the user on the Database. Use the below script to provide execute permission to the user on the database level:

use [DB_Name]
GO
GRANT EXECUTE TO [UserName]
GO

---

Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

Answer 2

You are right. I knew this but I forgot it (Getting old for this stuff). Thanks........................