Share via

Right permissions to view inside SQL Server logs - SQL Server 2016-2017

  • Article
  • 05/23/2018

Question

Wednesday, May 23, 2018 8:39 AM

Hi,

in order to view inside the SQL Server logs on a prod environment by SSMS, I've obtained from my customer a SQL login with securityadmin role, but I cannot execute xp_readerrorlog procedure.

Now, which right permissions should I have on SQL Server prod environment to access the logs?

Any suggests to me, please?

Thanks

All replies (8)

Wednesday, May 23, 2018 8:47 AM

https://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/

Add Login to security admin fixed server roles he can be able to read SQL Server errorlog file

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Wednesday, May 23, 2018 8:59 AM

Hi,

as I've written, I've a SQL login to access to the prod environment of my customer.

My SQL login belongs to the securityadmin role. This SQL login is created for me from my customer.

I'm trying to see inside the SQL logs from SSMS, but I've an error because I cannot execute the xp_readerrorlog procedure.

Now, is my post more clear? How can I solve this issue?

Thanks

Wednesday, May 23, 2018 9:11 AM

Now, is my post more clear? How can I solve this issue?

Your post is clear but unless you post the error message how do we know what actually is the issue ?

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Wednesday, May 23, 2018 9:13 AM

Well, what exact error are you getting?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Wednesday, May 23, 2018 9:22 AM

Hi Gentlemen,

I've alreary written about the error in a discursive manner!

If I cannot execute the system procedure means that I've authorization denied to execute the system stored procedure.

It shouldn't be necessary to explicit to major details to SQL experts.

Thanks

Wednesday, May 23, 2018 9:59 AM

Hi Gentlemen,

I've alreary written about the error in a discursive manner!

If I cannot execute the system procedure means that I've authorization denied to execute the system stored procedure.

It shouldn't be necessary to explicit to major details to SQL experts.

Thanks

No, it can mean lot of things and please don't use sarcastic comments and yes to give correct answer we do need details. Since you again did not posted error message as it came and changed it into your form I believe you have permission denied message

GRANT EXECUTE ON xp_readerrorlog TO user_nameuser name can be same as that of login
GO

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP

Wednesday, May 23, 2018 10:28 AM

Hi Shashank,

I shouldn't want to do a sarcastic comment.

I've said that towards SQL experts it can be possible talking in a discursive manner without saying necessarily too technical details. Generally it can not to do something refers to an authorization issue from a software or a person.

However, is not enough to have the securityadmin role to view inside SQL logs by SSMS? Does it occur also allowing the execution of the xp_readerrorlog system stored procedure?

Thanks

Wednesday, May 23, 2018 10:46 AM

I think you original post was clear enough. (But I did not see it until now.)

There are now rows in master.sys.database_permissions for xp_readerrorlog, thus only users to execute any procedure can run it. Such a permission does not follow with securityadmin, but you would have to be granted EXECUTE permission explicitly. (Or added to a role like sysadmin, but that is quite hefty.) I like to add that although the errorlog may include security-related messages, I don't think of the error log as something that falls within the duties of a security administrator.

As a piece of curio: membership in the bulkadmin role may also permit you to read the errorlogs. Not through the UI in SSMS directly, but you can query it with OPENROWSET(BULK) in this case. (But the permissions on the file may be a problem.)