Showing posts with label sqlerrorlog. Show all posts
Showing posts with label sqlerrorlog. Show all posts

Friday, July 17, 2015

Work adeventure : Permission to view sql error log (ONLY)

I had a situation where I had to give a user permission to view the sqlerror log only. I achieved by doing the following

  • 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 ;

Friday, June 19, 2015

query sql error log

Grab the information of sql error log in a table and query


--gather all the error in a temp table and query the temp table to find the specific error by text search or date range

CREATE TABLE #SQLErrorLog



(
 
LogDate DATETIME,

ProcessInfo VARCHAR(50),

Text VARCHAR(1500)



)
 
GO
 
INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 0



go
 
INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 1



go
 
INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 2



go
 
INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 3



go
 
INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 4



go
 
INSERT INTO #SQLErrorLog

EXEC xp_readerrorlog 5



go


 
SELECT * FROM #SQLErrorLog

WHERE LogDate >= CONVERT(datetime,'06/08/2015') AND LogDate < CONVERT(datetime,'06/20/2015')

and Text LIKE '%failed%' OR Text LIKE 'Error:%'

ORDER BY LogDate DESC

DROP TABLE #SQLErrorLog