2

I would like to give a user see SQL Server logs from GUI, not through xp_readerrorlog. I was just wondering if this was possible?

Thank you.

5

Yes, this is possible by creating a user with 'securityadmin' server role and then if required Deny his access to SQL Server Query Window through LOGON Trigger

enter image description here

USE [master]
        GO

    ----Create Login
    CREATE LOGIN [DBA_ErrorLogUser] 
    WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], 
    CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO

    --- Add server Add securityadmin   
    --  To view SQL Server error logs user must be a part of securityadmin server role
    EXEC master..sp_addsrvrolemember 
    @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
    GO

    --- Mapp user with login to grant access to logs
    CREATE USER [DBA_ErrorLogUser] FOR LOGIN [DBA_ErrorLogUser]
    GO

    --- Deny Alter to any Login 
    DENY ALTER ANY LOGIN TO DBA_ErrorLogUser 
    GO 

    --- Grant permission to view Sql Server Logs
    Grant EXECUTE ON master.sys.xp_readerrorlog TO DBA_ErrorLogUser 
    GO 


    --- Create a log on Trigger to deny access to Query Window
    IF EXISTS ( SELECT  *
                FROM    master.sys.server_triggers
                WHERE   parent_class_desc = 'SERVER'
                        AND name = N'Deny_QueryWindowLogin_Trigger' ) 
        DROP TRIGGER [Deny_QueryWindowLogin_Trigger] ON ALL SERVER
    GO

    Create TRIGGER Deny_QueryWindowLogin_Trigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

    DECLARE @data XML
    SET @data = EVENTDATA()

    DECLARE @AppName SYSNAME,
            @LoginName SYSNAME

    SELECT  @AppName = [program_name]
    FROM    sys.dm_exec_sessions
    WHERE   session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

    SELECT  @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')

    IF @AppName= 'Microsoft SQL Server Management Studio - Query' AND @LoginName = 'DBA_ErrorLogUser' 
        BEGIN
            ROLLBACK ; --Disconnect the session

        END 
END ;

Note: Must Read securityadmin Server Role Permissions

3

Be careful following this solution. The throw-away 1-liner at the bottom needs to be heeded. When you grant someone membership to the 'securityadmin' fixed server role, they will gain enormous power over your SQL instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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