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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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)', 'int') SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)', '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