Sql-server – ALTER DATABASE ARITHABORT configuration minimum privilege needed

alter-databaseconfigurationsql server

What is the minimum privilege needed to execute ALTER DATABASE {db_name} SET ARITHABORT ON?

I found that the privilege ALTER ANY DATABASE would suffice this, but I wonder if there isn't any other "smaller" privilege that does the same thing, because I think this permission is too broad and maybe I'll not have it in production environment.

Best Answer

Well, ended up I changed the user options, so new sessions start with ARITHABORT ON. It worker out for me.

Here's a link better explaining how: Make SqlClient default to ARITHABORT ON

I've used the "Server-wide" part of it.

Related Solutions

Sql-server – SQL Server Agent: QUOTED_IDENTIFIER

I discovered that the SQL Agent job sets the database options based on the compatibility level of the database and this can override the database options that have been set.

i.e. Database has database options of SET QUOTED_IDENTIFIER ON however the compatibiliy level of the database is set to SQL 2000 so that the SQL Job fails (i.e. when attempting to UPDATE rows in table that has filtered index on it).

Switch the compatibility level to SQL 2008 and then the SQL job will succeed.

Sql-server – Truncating table without using Alter Table permission

No sane DBA will ever allow such a procedure. This is a SQL injection privilege escalation vector. I can pass in the tablename 'x; exec sp_myfoo;' and voila.

There are basic issue:

table names are NVARCHAR, not VARCHAR
table names are length 128, not 60
sysname is a handy type to represent object names, is an alias for NVARCHAR(128)
tables are qualified by schema and name, not by name only
procedures that build dynamic SQL must properly quote the names, using QUOTENAME

Lets try again:

 CREATEPROCEDURE [dbo].[spTruncate]
    @schemaName sysname,
    @tableName sysname
 WITH EXECUTE AS CALLER
 AS
 SET NOCOUNT OFF;
 DECLARE @QUERY NVARCHAR(max);
 SET @QUERY = N'TRUNCATE TABLE ' + QUOTENAME(@schemaName) + '.' + QUOTENAME(@tableName );
 EXECUTE sp_executesql @QUERY;

No need to use execute as OWNER. As CALLER is fine, as long as the procedure is properly signed. Ask you DBA to sign the procedure following the standard procedure. See Module Signing and Signing Stored Procedures in SQL Server. Your DBA should know how to do this.

Best Answer

Related Solutions

Sql-server – SQL Server Agent: QUOTED_IDENTIFIER

Sql-server – Truncating table without using Alter Table permission

Related Question