Basic 1st line checks - need help with security

Question

Basic 1st line checks - need help with security

smackie1973

SSC Enthusiast

Points: 136
More actions
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

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

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

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

smackie1973 SSC Enthusiast Points: 136 More actions · Answer 2

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 5

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