locked
View Logs (READ ERROR LOGS) RRS feed

  • Question

  • Hi Team,

     

    One of User was raise a new Request he want to see sql server logs ( read error logs, ERROR LOGS) for this as per document we give to security admin, but i should not that access to that user

    because using security admin acces he changes the PW also right so for this reason i should not give this access to that user

    IS THERE ANY SPECIFIC access  to give the read error logs

     

    what permission i have to give that user please help me on this

    Really appreciate those guys.


    Thanks

     


    subu


    • Edited by subu999 Monday, November 14, 2011 5:19 PM
    Monday, November 14, 2011 5:05 PM

Answers

  • Hi,

     

    I'm afraid none of these statements will help you.

    The role you are creating has not been assigned any permissions (and you do not specify which database you are creating it in)

    Your GRANT EXECUTE statement is syntactically incorrect as you do not state the object that you wish to be able to execute with the role.

    Finally VIEW SERVER STATE does not allow users to view the error log, and you haven't referenced your role - which you couldn't anyway because VIEW SERVER STATE is a server-level permission rather than database-level.

    If you would reply to Erland's question about the actual result from trying to execute xp_readerrorlog we can help you.

     

    Ben

    • Marked as answer by Maggie Luo Tuesday, November 29, 2011 6:25 AM
    Tuesday, November 15, 2011 4:47 PM

All replies

  • You could create a user in the master database and then explicitly grant execute permissions on xp_readerrorlog. That should enable them to use this xp but they would not be able to access logs via the SSMS GUI as this calls sp_enumerrorlogs.

    Please note however that xp_readerrorlog is undocumented and therefore you should its use consider carefully before using it in production.

    Ben

    Monday, November 14, 2011 5:31 PM
  • Hi Ben,

     

    As per your suggestion i tried but no luck, That user not able to view logs

     

    Any body please suggest me.

     

    Tx


    subu
    Monday, November 14, 2011 5:36 PM
  • The user should be able to view logs by executing the T-SQL command xp_readerrorlog

    is that what they are trying to do? What error message do they receive?

     

    Ben

    Monday, November 14, 2011 5:46 PM
  • we are not getting any error message but that user not able to view the logs.,

    subu
    Monday, November 14, 2011 5:53 PM
  • we are not getting any error message but that user not able to view the logs.,

    So the user runs xp_errorlog and what happens? It returns an empty result set?

    Could you give the exact commands you issued to set this up?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, November 14, 2011 10:00 PM
  • Hi Team,

     

    i am planning to follow in this way.

     

    to view logs in sql server

    --CREATE A NEW ROLE

    CREATE ROLE db_executor

      

    --GRANT EXECUTE TO THE ROLE

    GRANT EXECUTE TO db_executor

      

    Use Master

    GRANT VIEW SERVER STATE TO "V002756"

     

    any thing wrong please corret me.

    Tx



    subu
    Tuesday, November 15, 2011 3:41 PM
  • Hi,

     

    I'm afraid none of these statements will help you.

    The role you are creating has not been assigned any permissions (and you do not specify which database you are creating it in)

    Your GRANT EXECUTE statement is syntactically incorrect as you do not state the object that you wish to be able to execute with the role.

    Finally VIEW SERVER STATE does not allow users to view the error log, and you haven't referenced your role - which you couldn't anyway because VIEW SERVER STATE is a server-level permission rather than database-level.

    If you would reply to Erland's question about the actual result from trying to execute xp_readerrorlog we can help you.

     

    Ben

    • Marked as answer by Maggie Luo Tuesday, November 29, 2011 6:25 AM
    Tuesday, November 15, 2011 4:47 PM
  • Check below link and hope this will help you.

    http://www.sqlservercentral.com/Forums/Topic939436-359-1.aspx#bm941314

     

     

    Wednesday, November 23, 2011 6:47 PM
  • This thread reminds me of the old saying "If all you have is a hammer, everything looks like a nail."

    The SQL error log files are [text files] stored in a folder on your server.  There's really no need to create logins, grant high level server permissions and/or execute [undocumented] extended stored procedures.

    1.) create a share on the server pointed directly at the log folder

    2.) grant [read only] access to that folder for the user that wants to read the files

    3.) remind the user if for any reason they somehow lock the file and SQL Server can't write to it, their permission will be revoked until hell freezes over - after explaining to the CIO why they deserve to keep their job. 

    Subu: Note that the log file contains sensitive information including IP address, protocols used to connect, authentication mode, startup parameters, file locations and in some cases login names and client IP addresses if a login fails.  Seriously, you need to trust this user completely before turning this information loose.  Personally, I wouldn't - I'd consider this to be privileged information for DBA use only.

    Wednesday, April 11, 2012 4:03 PM