Give user permission to read error log

Question

_{Sunday, June 7, 2015 3:24 AM}

Just like the title says.

Alan

All replies (2)

_{Sunday, June 7, 2015 4:58 AM ✅Answered}

Just like the title says.

Alan

If you add Login to security admin fixed server roles he can be able to read SQL Server errorlog. But be careful with the right and read about it

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

---

_{Sunday, June 7, 2015 3:27 AM}

USE [master]
GO

-- Drop the user if it exists
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ErrorLogReader')
DROP USER [ErrorLogReader]
GO

-- Drop the login if it exists
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ErrorLogReader')
DROP LOGIN [ErrorLogReader]
GO

-- Create a login who will get permission to read the error log
CREATE LOGIN ErrorLogReader WITH PASSWORD = 'C0mpl3xPa$$w@rd**'
GO

-- Deny connection ability to this login for security
DENY CONNECT SQL TO [ErrorLogReader]
GO

-- Create a user with this login 
CREATE USER ErrorLogReader FOR LOGIN ErrorLogReader
GO

-- Grant execute permission to the undocumented stored procedure xp_readerrorlog
GRANT EXECUTE ON xp_readerrorlog TO ErrorLogReader
GO

-- Create a database to test this in
CREATE DATABASE ErrorLogDB
GO

-- Switch to the new database
USE [ErrorLogDB]
GO

-- Create the ErrorLogReader user here as well
CREATE USER ErrorLogReader FOR LOGIN ErrorLogReader
GO

-- Create a stored procedure which will execute xp_readerrorlog
CREATE PROCEDURE usp_readerrorlog 
WITH EXECUTE AS 'ErrorLogReader'
AS
BEGIN
  EXEC xp_readerrorlog
END
GO

-- Create a special user to test permissions
CREATE USER TestUser WITHOUT LOGIN
GO

-- Grant the TestUser execute permissions on the stored procedure
GRANT EXECUTE ON usp_readerrorlog TO TestUser
GO

-- Test running under the test user's context
EXECUTE AS USER = 'TestUser'

-- Try executing xp_errorlog
EXEC xp_readerrorlog

-- You should get an error message like this:
/*
Msg 229, Level 14, State 5, Procedure xp_readerrorlog, Line 1
The EXECUTE permission was denied on the object 'xp_readerrorlog', database 'mssqlsystemresource', schema 'sys'.
*/

-- Try executing the stored procedure
EXEC usp_readerrorlog

http://www.sqlservercentral.com/Forums/Topic939436-359-1.aspx

Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue