SQL Server – Prevent Dropping Tables but Allow Creating Within Schema

azure-sql-data-warehousesql server

Is it possible to, within a specific Schema, allow a user to create a table, and select from it, but not allow them to Delete or update rows after initial creation, and also prevent them from dropping it ?

Create Table as Select requires select permissions for the Select, however, the Create Table requires Create table and Alter Schema on the Schema.

The Alter Schema permission also includes permissions to drop the table, which we don't want.

We are using Azure SQL Data Warehouse, so can't use Triggers..

Best Answer

Expanding on the suggestion of @Dominique Boucher.

You can do it by making a procedure that acts as proxy for the create statement and specifying an execute as clause in it. If you then only allow execute permissions to the users that you want, they can only create tables, not edit them. This way don't become owners (the execute as user becomes owner) and they can't edit the procedure itself.

GRANT EXECUTE ON [dbo].[TableBuilder] TO [Plebby]

Below an example, with some checking/error handling, but you would need to expand on it to suit your needs. You would create this procedure on a highly priviliged user.

CREATE PROCEDURE TableBuilder
@schemaName sysname,
@tableName sysname,
@tableScript nvarchar(max)
WITH EXECUTE AS 'dbo'
AS
BEGIN
DECLARE @ErrorMessage NVARCHAR(4000);

IF SCHEMA_ID(@schemaName) IS NULL
BEGIN
    SET @ErrorMessage = 'Schema with name ' + @schemaName + ' does not exist.';
    RAISERROR(@ErrorMessage, 16, 0);
    RETURN
END

IF OBJECT_ID(@schemaName + '.' + @tableName, 'U') IS NOT NULL
BEGIN
    SET @ErrorMessage = 'Defined table ' + @schemaName + '.' + @tableName + ' already exists';
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

IF (@schemaName + '.' + @tableName <> (SELECT SUBSTRING(@tableScript, (CHARINDEX('TABLE', @tableScript) + 6), (LEN(@schemaName) + LEN(@tableName) + 1))))
BEGIN
    SET @ErrorMessage = 'Schema and/or tablenames in creation script do not match with ' + @schemaName + '.' + @tableName;
    RAISERROR(@ErrorMessage, 16, 0)
    RETURN
END

EXEC(@tableScript)

RETURN 0
END

A user with execution rights on the above procedure can use it to execute a valid CREATE TABLE statement. And you can add more checks/constraints (i.e. the allowed schemas). And should do so since the execution is dynamic SQL. Depending on the user rights of your:

WITH EXECUTE AS 'user'

You can get some nasty rights escalation if you don't control your input. I've tested the above procedure on SQL Server 2008, 2012 and 2017. I don't have an AzureDB instance available for testing though.

Related Question