HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMinor

View permission to SQL Server logs on SSMS GUI

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
ssmslogssqlpermissionviewservergui

Problem

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.

Solution

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

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

Code Snippets

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 ;

Context

StackExchange Database Administrators Q#82145, answer score: 6

Revisions (0)

No revisions yet.