Permission To Read Sql Error Log
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and grant permission to view sql server logs policies of this site About Us Learn more about Stack Overflow the the execute permission was denied on the object 'xp_readerrorlog' company Business Learn more about hiring developers or posting ads with us Database Administrators Questions Tags Users Badges Unanswered grant execute on xp_readerrorlog Ask Question _ 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. Join
Grant Xp_readerrorlog
them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top View permission to SQL Server logs on SSMS GUI up vote 2 down vote favorite I would like to give a user see SQL Server logs from GUI, not through xp_readerrorlog. permissions to view sql server logs I was just wondering if this was possible? Thank you. sql-server sql-server-2008 sql-server-2008-r2 ssms error-log share|improve this question asked Nov 7 '14 at 13:13 Stackoverflowuser 699618 add a comment| 1 Answer 1 active oldest votes up vote 5 down vote accepted 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_
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLearnDownloadsCommunity Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Give user permission to read error log SQL Server > SQL Server Security Question 0 Sign in to vote sqlagentreaderrole Just like the title says. Alan Sunday, June 07, 2015 3:24 AM Reply |
Sp_readerrorlog
Quote Answers 0 Sign in to vote Just like the title says. Alan If you add Login to security admin fixed
Sql Server Error Log
server roles he can be able to read SQL Server errorlog. But be careful with the right and read about itPlease mark this reply as answer if it solved your issue or vote as helpful http://dba.stackexchange.com/questions/82145/view-permission-to-sql-server-logs-on-ssms-gui if it helped so that other forum members can benefit from it My Technet Wiki Article MVP Proposed as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, June 08, 2015 9:03 AM Marked as answer by Lydia ZhangMicrosoft contingent staff, Moderator Monday, June 15, 2015 8:58 AM Sunday, June 07, 2015 4:58 AM Reply | Quote All replies 0 Sign in to vote USE [master] GO -- Drop the user if https://social.msdn.microsoft.com/Forums/windowsserver/en-US/9349c1a2-0039-4ca8-8951-2a08c5cd1758/give-user-permission-to-read-error-log?forum=sqlsecurity it exists IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'ErrorLogReader') DROP USER [ErrorLogReader] GO -- Drop the login if it exists IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ErrorLogReader') DROP LOGIN [ErrorLogReader] GO -- Create a login who will get permission to read the error log CREATE LOGIN ErrorLogReader WITH PASSWORD = 'C0mpl3xPa$$w@rd**' GO -- Deny connection ability to this login for security DENY CONNECT SQL TO [ErrorLogReader] GO -- Create a user with this login CREATE USER ErrorLogReader FOR LOGIN ErrorLogReader GO -- Grant execute permission to the undocumented stored procedure xp_readerrorlog GRANT EXECUTE ON xp_readerrorlog TO ErrorLogReader GO -- Create a database to test this in CREATE DATABASE ErrorLogDB GO -- Switch to the new database USE [ErrorLogDB] GO -- Create the ErrorLogReader user here as well CREATE USER ErrorLogReader FOR LOGIN ErrorLogReader GO -- Create a stored procedure which will execute xp_readerrorlog CREATE PROCEDURE usp_readerrorlog WITH EXECUTE AS 'ErrorLogReader' AS BEGIN EXEC xp_readerrorlog END GO -- Create a special user to test permissions CREATE USER TestUser WITHOUT LOGIN GO -- Grant the TestUser execute permissions on the stored procedure GRANT EXECUTE ON usp_readerrorlog TO TestUser GO -- Test running under the test user's context EXECUTE AS USER = 'TestUser' -- Try executing
Maintain Jobs, View Logs Any Tools Avail To View Transaction Logs? View Button For Step Output Logs Greyed Out Backup And Restore Database Tat Able To View All The Transaction Logs Granting Permission For A VIEW? Permission On View Not On Table Granting Permission To Create View http://www.bigresource.com/MS_SQL-permission-to-view-error-logs-HbWOguvJ.html Permission To View The Posted Reports Giving Permission To View CERTAIN Records How To Grant Permission To https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/ View Stored Procedures? SYSAdmin Users Do Not Have Permission To See Records In A View? T-SQL (SS2K8) :: Deny View On Database And Select Permission? SQL Server 2008 :: SSRS Report View Permission SQL Security :: View Definition Permission On Target Database SQL 2012 :: DENY Permission On Column Subsequently Reference In View? Permission On View. Is There Way To Avoid Granting Persmission On The Underlying Table? SQL Server Admin sql server 2014 :: How To Grant User Permission To View Specific Views DTS Error Logs. Error Logs SQL Error Logs Error Logs For JOB Error Logs OLE DB Error: OLE DB Or ODBC Error: You Do Not Have Permission To Run 'SP_TRACE_CREATE'.; 42000 DB Engine :: Write Logs To Windows Event Logs? Out Of Control Error Logs So Confused Need Help Regarding Error Logs And SQL SSIS Error Logs Executive Error Logs---very Urgent SQLServer2000 - Error Logs Change Loc How To Write Error Logs In Notepad ADVERTISEMENT SQL to view sql Server Logs (error Logs) Aug 28, 2001 When looking at SQL Server error logs, I noticed that the current errorlog had grown to 1MB whereas most of the files are only a few KB.QUESTION 1: Can I set the max size for an error log file? If so, how?QUESTION 2: If not, is there a workaround?Thanks View 1 Replies View Related Cannot View Server Logs Jun 12, 2007 I have SQL Server 2005 Express on a Windows XP SP2 machine. Everything works fine and I have several dbs running. How ever I was wanting to optimize some programs and needed to watch the server and see what it is processing. I log into SQL Management Studio Express and when I go to Management->Server Logs I see the logs and the Activity Monitor. If I right click on the log all I see is Refresh. If I doule click nothing happens. I can view the Activity Monitor and the processes going. I am sure it is a permission thing or something along those lines. I can not for the life of me figure it and have spent a couple days on google and these forums. I have tried with the SA, my defined users, and the local windows administrator. Is log viewing not allowed in SQL Server Express? Thanks!Michael View 6 Replies View Related Maintain Jobs, View Logs Mar 13, 2006 I have a user who needs to check whether 2 jobs have run on a SQL Server 2000 instance, and be able to view logs.Is there a way
| 2 | 3 | More > Monitoring ProblemOne of the issues I have is that the SQL Server Error Log is quite large and it is not always easy to view the contents with the Log File Viewer. In a previous tip "Simple way to find errors in SQL Server error log" you discussed a method of searching the error log using VBScript. Are there any other easy ways to search and find errors in the error log files? SolutionSQL Server 2005 offers an undocumented system stored procedure sp_readerrorlog. This SP allows you to read the contents of the SQL Server error log files directly from a query window and also allows you to search for certain keywords when reading the error file. This is not new to SQL Server 2005, but this tip discusses how this works for SQL Server 2005. This is a sample of the stored procedure for SQL Server 2005. You will see that when this gets called it calls an extended stored procedure xp_readerrorlog. CREATEPROC[sys].[sp_readerrorlog](
@p1INT=0,
@p2INT=NULL,
@p3VARCHAR(255)=NULL,
@p4VARCHAR(255)=NULL)
AS
BEGIN
IF(NOTIS_SRVROLEMEMBER(N'securityadmin')=1)
BEGIN
RAISERROR(15003,-1,-1,N'securityadmin'