locked
User Securty Access RRS feed

  • Question

  • Hi All,

    There request from user , where user wan't access to sql server with only privilage to check Agents error logs and Job History for analysis purpose , so can anyone help me with appropriate login for such user.

    Note : Can't give admin right on server to such user.

    Regards,

    Anish


    Asandeen

    Tuesday, August 6, 2013 2:33 AM

Answers

  • Hi Anish_20,

    Could you confirm us that whether you want the user has his/her login to logon to the sql server but only read error logs?

    If yes, you could refer below links to accomplish this goal.

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

    If you don't want user has accesss to logon to sql server, as the SQL Agent error log are stored in a log folder on your server.  You could create a particular folder for the SQL Agent error log.Then do the following:

    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. 

    Reminder: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.

    Thanks

    Candy Zhou


    • Edited by Candy_Zhou Wednesday, August 7, 2013 11:27 AM edit
    • Marked as answer by Allen Li - MSFT Tuesday, August 13, 2013 11:13 PM
    Wednesday, August 7, 2013 11:26 AM

All replies

  • http://msdn.microsoft.com/en-us/library/ms188283.aspx

    You can use below sql agent role in MSDB as per your requirement.

    • SQLAgentUserRole

    • SQLAgentReaderRole

    • SQLAgentOperatorRole



    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Tuesday, August 6, 2013 3:54 AM
  • Hi Anish_20,

    Could you confirm us that whether you want the user has his/her login to logon to the sql server but only read error logs?

    If yes, you could refer below links to accomplish this goal.

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

    If you don't want user has accesss to logon to sql server, as the SQL Agent error log are stored in a log folder on your server.  You could create a particular folder for the SQL Agent error log.Then do the following:

    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. 

    Reminder: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.

    Thanks

    Candy Zhou


    • Edited by Candy_Zhou Wednesday, August 7, 2013 11:27 AM edit
    • Marked as answer by Allen Li - MSFT Tuesday, August 13, 2013 11:13 PM
    Wednesday, August 7, 2013 11:26 AM