User permission to view error log

  • I have a login that has Read and write permissions on user databases, but I would also like this user to be able to view the SQL Error logs.

    I know that securityadmin will grant this option to the login, but it unfortunately comes with other permissions like 'manage logins' and 'CREATE DATABASE permissions' - which I don't want this user to have.

    Any ideas on how I can grant only the viewing of the error logs?

    Thanks,

  • This can be done by granting execute permissions on the undocumented system stored procedure xp_readerrorlog.

    Two caveats are: 1) undocumented stored procedures can change in future editions of SQL Server and 2) by allowing an application user to read the error log, you are creating a security vulnerability.

    As you probably know, the error log shows very specific information about the SQL Server instance, the database names, the version, and so forth.

    I would recommend reading this article:

    http://www.sommarskog.se/grantperm.html.

    He discusses impersonation and the various security risks involved. In fact, the recommends against the method that I used to make this work.

    Anyway, the script is below. I wrote this in SQL Server 2008 Express, but I think it should work in 2005.

    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

    -- Revert back to original context

    REVERT

    -- Switch back to master

    USE [master]

    GO

    -- Clean up everything we created

    DROP DATABASE [ErrorLogDB]

    GO

    -- Drop the ErrorLogUser user from the master db

    DROP USER ErrorLogReader

    GO

    -- Drop the ErrorLogUser login from the master db

    DROP LOGIN ErrorLogReader

    GO

Viewing 2 posts - 1 through 2 (of 2 total)

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