ServerAdmin Server Role - Reading Error Logs

Question

ServerAdmin Server Role - Reading Error Logs

Paul A

SSC Eights!

Points: 985
More actions
September 24, 2007 at 3:36 am

#178764

Hello,
I am creating some sql login accounts with limited privledges.
One of the privledges they need is to be able to read the SQL Error logs.
In order to do this, I have added them to the 'ServerAdmin' fixed server role.
Unfortunatelly,I get an error when opening the error logs in Management Studio: EXECUTE PERMISSION denied on xp_readerrorlogs in database msqlsystemresources. Is there any way around this, as I don't want to change the privledges in the mssqlsystemresources database?
Why does the online books indicate that you can read error logs, when you can't execute this procedure?
Thanks in advance for any advice.
Paul

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

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

michaela SSCertifiable Points: 5649 More actions · Answer 1

I have to get used to this new website format...sorry, I do not have your message in front of my eyes right now, hope this will answer your question;

Viewing logs from within sql management studio requires the login to be member of the securityadmin server role which is actualy a little too much. I would say to:

1-make the login user of master database with a minimum set of permissions, lets say public

2- run the following:

deny

ALTER ANY LOGIN TO yourlogin;

GO

GRANT

EXECUTE ON master.sys.xp_readerrorlog TO yourlogin;

GO

To grant exec permission to a login, that login has to be user of the db that includes the sp.

Now your login should see the logs.

However, since the logs are flat files, I would say it's better to make the folder where the log files are residing shareble and give read permissions (Win) to your guys.

Good luck.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 2

Paul, I don't know where you've read that Server Admin can execute this procedure, but when you execute the code in a query window the error message tells you that "Only members of the securityadmin role can execute this stored procedure"

Markus

[font="Verdana"]Markus Bohse[/font]

Paul A SSC Eights! Points: 985 More actions · Answer 3

Thats works perfectly, thanks Michaela for your help.

I didn't know it was possible to change permissions on a proc in mssqlsystemresource like this, as the stored procedure is not visible from the master database.

I did actually mean to specify 'SecurityAdmin' in original post by the way.

Thanks all.

cooolguymca Valued Member Points: 63 More actions · Answer 4

i have a database in sqlserver 2005.

Somebody deleted some records in my table and i want to

the deteled records.How can i do this.Can i recover those records using log files.

How to read log files