Thursday, November 17, 2011

Giving Access to Users for Reading Error Log without Providing Server Level Permission

For server auditing some users may request for read error log. As a DBA we have to think twice before giving server level access to any developer or auditor or any other user for reading error log, or we have to think what's the best alternate way to give access to user for reading error log without giving server level access?

We can provide access for read error log through granting the execute permissions on the undocumented extended stored procedure 'xp_readerrorlog'.

This extended stored procedure returns the list of all error logs with their last change date.This is very simple to give access to users in 2 steps but note that xp_readerrorlog is undocumented so therefore you should use consider it carefully in production. 

Step 1: Map the user to master database. 

Step 2: Then grant execute permissions on xp_readerrorlog. (Grant execute permission to the undocumented stored procedure xp_readerrorlog)

USE Master
GO
GRANT EXECUTE ON xp_readerrorlog TO [User Name]
GO

NOTE: Users would not be able to access logs via management studio graphically, they can only access the log through query window running below query.

EXEC master..xp_readerrorlog
SQL SERVER PERMISSIONS. View the SQL Server Error Log. How to manage the SQL Server errorlog. How to give permission to the SQL Server errorlog. User access log to SQL Server. SQL Server Error Logs. How to allow access for a sql server user. Huge Errorlog with SQL Server. SQL Server query errors log.

0 comments:

Post a Comment