Post reply

Basic 1st line checks - need help with security

  • March 15, 2006 at 2:08 am

    #113234

    I am in the process of handing over some basic daily database checks (checking db/log sizes, backups, error log etc.) to our 1st line support team.

    They currently have domain admin rights (for other windows based tasks) but this also gives them full control of SQL Server via windows authentication.

    I am looking to create a read-only SQL Server login (called 1stLine) for each of the team (3 people) that will allow them to view the details i want, without running the risk of them deleting something they shouldn't!

    I can get this to work for viewing the database size/free space etc. using the Taskpad view.

    My problem is that i cannot get the user account to see the SQL Server Error log or the Backup Jobs under the SQL Server Agent.

    Does anyone know if there is a way around this?

    I am trying to keep this as quick and simple as possible using Enterprise Manager to access the info.

    I am using SQL Server 2000 Enterprise Edition.

    Any help appreciated.

    Cheers

    Scott

  • March 15, 2006 at 3:55 pm

    #626919

    Scott,

    You say they are Windows Administrators and as Windows Administrators they have access to the Event Viewer and they are comfortable with it!

    Events that are logged to SQL server log and Agent log are also logged to Windows Application log. You can also configure jobs to write to Windows application log and also auditing user's success and failed logins if Audit Level is set to both. They have to select source MSSQLSERVER or SQLSERVERAGENT. The RAISEERROR message will also appear there if you do RAISEERROR ...WITH LOG in the applications.

    Regards,Yelena Varsha

  • March 16, 2006 at 3:40 am

    #626968

    Hi Yelena

    Thanks for the update.

    To be honest, i forgot about using the windows event log

    However, it would stil be handy for the 1st line team to be able to see the SQL Error Log via Enterprise Manager if they have to quickly troubleshoot any problems at evenings and weekends. I work in a call center, where if the database is down for 5mins, people are not making money, so speed up diagnosis is essential.

    I looked at trying to create a new role and then add the rights, but i didn't get anywhere with it.

    Cheers

    Scott

  • March 16, 2006 at 7:05 am

    #626998

    Scott

    Also, if they're domain admins, they'll be able to see the SQL error logs (errorlog, errorlog.1 etc) as text files, without using Enterprise Manager.

    John

  • March 16, 2006 at 10:52 am

    #627105

    Yes, but they may not always be able to open them, at the least most current one, as SQL Server (or Agent) may have them open.

    K. Brian Kelley
    @kbriankelley

  • March 16, 2006 at 11:04 am

    #627112

    I think we are talking about 2 different scenarios:

    1. Everyday Monitoring with Read-Only login for SQL Server - first-line support may use Event Viewer.

    2. Quickly Troubleshooting Problems: first-line support will connect using their Domain Admin high-privilege Windows login and they will be able to see logs and everything else and do what they need to do. In case of this type of events I would like my support to have admin login, not read-only.

    Regards,Yelena Varsha

Viewing 6 posts - 1 through 5 (of 5 total)

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