1

Database administration is not a strong point of mine, and I was wondering if a user with read only access can change the server settings.

In their query I can see this at the beginning:

SET ARITHABORT OFF
SET ANSI_WARNINGS OFF

I'm worried because we have been having SQL database deadlock issues and performance issue, and Microsoft say SET ARITHABORT OFF should be SET ARITHABORT ON

Thanks.

  • Welcome to the site, what is the MS SQL Server version(x,y,z)? – Md Haidar Ali Khan Apr 18 at 4:22
  • Hi @MdHaidarAliKhan it's 13.0.4001.0 from the looks of it – chris c Apr 18 at 4:25
1

Both the settings that you have mentioned in the question requires membership in public role, which is true in almost all the cases. You may refer below screenshot from the login window:

Login Properties Server Role

Kindly refer microsoft site for both of these as below:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql?view=sql-server-2017

You may also refer article from Pinal Dave on this setting. There is another wonderful article from Mr. Ahmad Yaseen,

I hope above helps.

  • Hi thanks for the response, I just checked and they do have public role. So I guess that means they have set that option to off? – chris c Apr 18 at 4:41
  • Thats right and you also need to note that this is local option inside a procedure/function. Refer the link which I have provided in my answer. – Learning_DBAdmin Apr 18 at 4:42
  • Thanks alot for your help, will upvote when rep is higher :) – chris c Apr 18 at 4:45
  • Remember that everyone has the public role (you can't escape it). So this is just another way to say that these commands are available to everybody. And since they are just local settings (for the session or further scoped if inside a code object), I guess the reasoning is that they don't affect the "outside world". Of course SET commands can affect query plans/performance and concurrency aspects, which in turn can affect others. But so can any type of code you run. :-) – Tibor Karaszi Apr 18 at 7:23
1

As I am not discarding anything of Mr. @Learning_DBAdmin, One thing I would like to say that as you said that Build Version is 13.0.4001.0, which is the SQL Server 2016 Service Pack 1 (SP1). And here the SQL Server 2016 Compatibility Level Designation is 130. As per MSDN BOL documentation here Setting ANSI_WARNINGS to ON implicitly sets ARITHABORT to ON when the database compatibility level is set to 90 or higher. If the database compatibility level is set to 80 or earlier, the ARITHABORT option must be explicitly set to ON.

  • Hi Md Haidar Ali Khan the compatibility level is set to 110, so should arithabort be on or is it ok to be off? THanks :) – chris c May 7 at 1:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.