- Create a login DBA_error_logUser in the server
- Added the login to the securityadmin server role ( to view sql error log the login must be the member of this server role)
- Created a user DBA_error_logUser with map to the login to grant access to the error logs
- Deny Alter to any Login
- Grant permission to view Sql Server Logs for the user
- Create a log on Trigger to deny access to Query Window
---Create a login DBA_error_logUser in the server
use [master]
GO
CREATE LOGIN [DBA_ErrorLogUser] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO
--Added the login to the securityadmin server role
EXEC master..sp_addsrvrolemember @loginame = N'DBA_ErrorLogUser', @rolename = N'securityadmin'
GO
--Created a user DBA_error_logUser with map to the login to grant access to the error 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 for the user
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 ;
