locked
The EXECUTE permission was denied on the object TableExists RRS feed

  • 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 6, 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 6, 2013 7:55 PM
    Friday, December 6, 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 6, 2013 7:55 PM
    Friday, December 6, 2013 7:46 PM
  • You are right. I knew this but I forgot it (Getting old for this stuff). Thanks........................
    Friday, December 6, 2013 7:55 PM