Grant Execute Permission on sp_audit_write

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    I got "sp_audit_write" working but only with ids granted to SYSADMIN.  Looking at Microsoft documents, the only permission required is "Requires membership in the public database role".  The stored procedure is in database mssqlsystemresource which I cannot find a way to grant permission to it. I added the login to every database public role on the server and still no good.

    Here is the message I get
    Msg 229, Level 14, State 5, Procedure sp_audit_write, Line 1
    The EXECUTE permission was denied on the object 'sp_audit_write', database 'mssqlsystemresource', schema 'sys'.

    How do I grant execute permission to  'sp_audit_write'?

    David Bird

  • SQLRNNR

    SSC Guru

    Points: 281252

    David Bird - Friday, March 31, 2017 11:50 AM

    I got "sp_audit_write" working but only with ids granted to SYSADMIN.  Looking at Microsoft documents, the only permission required is "Requires membership in the public database role".  The stored procedure is in database mssqlsystemresource which I cannot find a way to grant permission to it. I added the login to every database public role on the server and still no good.

    Here is the message I get
    Msg 229, Level 14, State 5, Procedure sp_audit_write, Line 1
    The EXECUTE permission was denied on the object 'sp_audit_write', database 'mssqlsystemresource', schema 'sys'.

    How do I grant execute permission to  'sp_audit_write'?

    Out of curiosity has somebody maybe tried to harden the instance and revoked various permissions to the public role?

    Second question, has the USER_DEFINED_AUDIT_GROUP action been added to a server audit specification?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    It was not harden, unless that is the new default for SQL 2012

    USER_DEFINED_AUDIT_GROUP was enabled on the server audit and then I went back and added to the Database Audit. Permission still not being granted.

    David Bird

  • SQLRNNR

    SSC Guru

    Points: 281252

    Does the user/login have any explicit denies? E.g. deny execute?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    No denies

    Just db_datareader, db_datawriter, and execute permission on the user database.

    David Bird

  • Sue_H

    SSC Guru

    Points: 90793

    That is odd. I checked on a new 2014 instance and it was the same for whatever reason. Docs say public has permission but it doesn't. Was able to get the same error.
    You can grant permissions to public with: GRANT EXECUTE on sys.sp_audit_write to Public
    As a side note - All users are members of public so you don't need to add them. You won't get an error but if you check now, none of the users you added will be explicitly in that role. You can't add or remove members or drop the role. You can just change the permissions for the role.

    Sue

  • David Bird

    SSCarpal Tunnel

    Points: 4669

    Thank You, that was the problem the PUBLIC permission was missing.  I Granted execute permission on the Extended Stored Procedure in the Master database to the application id and it worked. Maybe Microsoft removed public permission for a reason so not putting it back.

    Thank You

    David Bird

  • SQLRNNR

    SSC Guru

    Points: 281252

    Yeah that is odd. Glad you figured it out.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply