locked
Grant SQL Server Agent and System log view to non admin user. RRS feed

  • Question

  • I have a SQL dev that has requested view permission to the SQL server logs.  I don't want to give this user local or admin access to the server as there have been issues with running scripts against incorrect databases.

    I have tried agentreader roll in MSDB but that isn't enough. Does anyone have any suggestions.  I am open to shipping the logs to him but am unsure of a best practice way of doing this in "real-time". 

    Any help would be appreciated.

    SQL Server 2008 Std R1 SP 2

    Thursday, March 15, 2012 6:31 PM

Answers

  • Wrap xp_readerrorlog in a stored procedure in master. Create a certificate and sign the procedure with the certificate. Create a login from the certificate (this is not a login that can log in) and grant this login permission to run sp_readerrorlog (appears to membership in securityadmin).

    For more details on this technique, see this article on my web site:
    http://www.sommarskog.se/grantperm.html


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Uwe RickenMVP Friday, March 16, 2012 11:40 AM
    • Marked as answer by amber zhang Monday, March 26, 2012 3:19 AM
    Thursday, March 15, 2012 8:32 PM