Grant Execute Permission on sp_audit_write

Question

Grant Execute Permission on sp_audit_write

David Bird

SSCarpal Tunnel

Points: 4669
More actions
March 31, 2017 at 11:50 am

#322593

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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 More actions · Answer 2

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: 281334 More actions · Answer 3

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 More actions · Answer 4

No denies

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

David Bird

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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 More actions · Answer 6

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: 281334 More actions · Answer 7

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