Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

Our development team is by policy not allowed to be an administrator on our sql server instance. This instance is for development and testing. I would like access to the sql server logs for this instance but you have to be an administrator to open these. Is there a stored procedure, or table, that the administrator can grant access to without giving me admin rights?

I was thinking if there was a sp_XXXX the admin could create a stored procedure that called that function using their own rights, and just grant us access to that. does anyone know if there is an sp, table, or system table where the logs are accessible?

share|improve this question

2 Answers

up vote 4 down vote accepted

Do you mean the data logs or the error logs?

The data log can be read with select .. from fn_dblog
The error log can be read with exec xp_readerrorlog (gives access to both sql and agent error logs)

Both of them are undocumented but well know function/procedure. Your admin should create a wrapper procedure, grant you execute on that procedure, and then use code signing to grant the needed permissions to that procedure. See Signing Activated Procedures for an example on how to sign a procedure.

share|improve this answer
I'm trying to watch for and diagnose timeouts. our dba says that some timeouts would be logged and some wouldn't. One of these would cover logged timeouts, right? – Maslow Oct 28 '09 at 16:57
timeout of...what? Failed login attempts would be covered by errorlog, if the server is configured to log them. statement execution timeouts are not logged anyway, since is not the server that decide to time out but the client. – Remus Rusanu Oct 28 '09 at 17:38
Would an openDataSource call to another server that timedout trigger anything in an error log? – Maslow Oct 28 '09 at 17:53
Most likely it won't. – Remus Rusanu Oct 28 '09 at 21:03
up vote 1 down vote

You have to be an administrator to open these only if they are stored in the default location, which is something like C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG. This is a really dumb place to store them, not only because you have to be admin to access them, but you also should not have constantly growing files on C:. When you run out of space there, bad things happen.

One of the first things I do on a SQL Server install is move the log, usually to the same spot (in a folder) where my database files and transaction logs are. There's more room there, hopefully its on a SAN or something so its quicker, its hopefully RAID 10 so they are safer, and you don't have to be admin to view them.

You do this by specifying a -e startup parameter for SQL Server, specifying where you want the logs to go.

-eF:\mssql\mylog\ERRORLOG  (or use whatever location you want)

Do this in SQL Server Configuration Mgr. Right Click SQL Server (MSSQLSERVER) -> Properties -> Advanced -> Startup Parameters

Look here also http://support.microsoft.com/kb/224071

Cheers

share|improve this answer
+1 this is one of the possible solutions my dba came up with, however the dba seemed to think that some timeouts are logged which Remus postulated is false. – Maslow Oct 29 '09 at 0:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.