none
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.

    Friday, December 06, 2013 6:58 PM

Answers

  • 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

    • Marked as answer by DCarlos Friday, December 06, 2013 7:55 PM
    Friday, December 06, 2013 7:46 PM

All replies

  • 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

    • Marked as answer by DCarlos Friday, December 06, 2013 7:55 PM
    Friday, December 06, 2013 7:46 PM
  • You are right. I knew this but I forgot it (Getting old for this stuff). Thanks........................
    Friday, December 06, 2013 7:55 PM