UPDATE PERMISSION without ALTER permission

Question

I have a stored procedure, that inserts some data into a table. At the end, I want to UPDATE STATISTICS on that table. The user which is executing the procedure, does not have sufficient permissions to run UPDATE STATITICS. Now, my idea was to create a stored procedure, which is executes as owner (has sufficient permissions) and grant the user execute permission on this stored procedure. So far it works, but if I execute the procedure with

EXEC dwh.sp_UpdateStatistics @schemaName = 'dwh', @tableName = 'dim_Assets;SELECT 123;'

it returns 123.

I have read about SQL injection, but I could not figure out how to solve this issue.

This is the procedure:

CREATE PROCEDURE dwh.sp_UpdateStatistics @schemaName VARCHAR(10) = NULL, @tableName VARCHAR(50) = NULL
WITH EXECUTE AS owner
AS

DECLARE @updateStatistics NVARCHAR(MAX);

SET @updateStatistics = N'UPDATE STATISTICS ' + @schemaName + '.' + @tableName;

EXEC sp_executesql @updateStatistics;

Could you please help me out to find a way how to update the statistics without granting any more permissions and without having the risk of SQL injections. (It is not that they are expected, it is fully inhouse, but it is simply for the sake of having a good sleep and the feeling of "doing it the correct way") Thank you! (I have SQLServer2017 but I cannot create a post with this tag.)

Slight detour but the sp_ prefix should be avoided. It is reserved for MS procedures. Either choose a different prefix, or even better no prefix at all. https://sqlperformance.com/2012/10/t-sql-queries/sp_prefix — Sean Lange, Aug 16 '19 at 15:50
Thank you for that information! was not aware of it and I will change it accordingly. Actually, I never thought that would have an impact. — Json, Aug 16 '19 at 17:45

score 1 · Accepted Answer · answered Aug 16 '19 at 15:45

Use QUOTENAME. I also suggest using the appropriate data types for the dynamic objects:

CREATE PROCEDURE dwh.sp_UpdateStatistics @schemaName sysname, @tableName sysname
WITH EXECUTE AS owner
AS BEGIN

    DECLARE @updateStatistics NVARCHAR(MAX);

    SET @updateStatistics = N'UPDATE STATISTICS ' + QUOTENAME(@schemaName) + N'.' + QUOTENAME(@tableName) + N';';

    EXEC sp_executesql @updateStatistics;
END;

I also removed the = NULL from the parameters, as it made no sense to make them optional (as the value of @updateStatistics would be NULL).

If you want to make it so that the code won't run if the object doesn't exist, check using something like INFORMATION_SCHEMA:

CREATE PROCEDURE dwh.sp_UpdateStatistics @schemaName sysname, @tableName sysname
WITH EXECUTE AS owner
AS BEGIN

    DECLARE @updateStatistics NVARCHAR(MAX);

    SELECT @updateStatistics = N'UPDATE STATISTICS ' + QUOTENAME(T.TABLE_SCHEMA) + N'.' + QUOTENAME(T.TABLE_NAME) + N';'
    FROM INFORMATION_SCHEMA.TABLES T
    WHERE T.TABLE_SCHEMA = @schemaName
      AND T.TABLE_NAME = @tableName;

    EXEC sp_executesql @updateStatistics;
END;

This will result in @updateStatistics having a value of NULL if the object doesn't exist, and then no (dynamic) SQL will be run.

And let's add to this - there is absolutely no reason to define the local variable as nvarchar(max). — SMor, Aug 16 '19 at 20:04
Why is that? Yes, I could use a number, since the variable is the schema and table name, but at least for storage space, I thought it does not make any diference, since varchar(max) only takes as much space as it requires. — Json, Aug 19 '19 at 05:43
For more information regarding the nvarchar(max) comment of @SMor, I found this link: [varchar(max) everywhere?](https://stackoverflow.com/questions/2091284/varcharmax-everywhere) — Json, Oct 29 '19 at 08:54

UPDATE PERMISSION without ALTER permission

1 Answers1